PosgtreSQL procedure

Question

I am new to postgres function. My requirement is to run a function on each and every table present in particular schema in postgres database but I am facing a problem because the function is returning only a single record each time the function calls while my query should returns "n" number of records for each table the function calls. Ahh it might be confusing- See code-

create or replace function name_list(schema text, tablename text) 
  returns text
as $body$
declare
  result text;
  query text;
begin
  query := 'SELECT "names"  FROM ' || schema || '.' || tablename;
  RAISE NOTICE '"%"' , query;
  execute query into result;
  return result;
end;
$body$
language plpgsql;

copy(select 
  table_name, 
  name_list(table_schema, table_name)
  from information_schema.tables) to 'C:\test\name_list.csv' DELIMITER ',' CSV HEADER;

I read few of the things like set of, loop and tried to execute but still no luck. Any help would be appreciated.

Is your requirement "to run a function"? Or is it to return certain data in a certain format? (What data and what format exactly?) Ideally, requirements outline the *goal*, not the means to achieve it (without need). You certainly don't need a function for this. — Erwin Brandstetter, Sep 06 '20 at 21:26
Hey Erwin, My requirement is to get a output in correct format. Actually the problem is with return type. It is giving me only 1 name for each table it runs but the thing is that it contains "n" number of names for each table. So, i want all the names with the name of that table. Output be like- Table name | names (these 2 columns). (function should return output of this query "select table_name, names from table_name" — A gupta, Sep 07 '20 at 08:42

Игорь Тыра · Accepted Answer · 2020-09-05T18:37:44.093

1

your function must be a set-returning

create or replace function name_list(schemaname text, tablename text) 
  -- function will return a set
  RETURNS SETOF text
language 'plpgsql'
as $body$
declare
  _query text;
begin
  -- correct way to format the dynamic sql
  _query := FORMAT('SELECT "names" FROM %I.%I;', schemaname, tablename);
  RAISE NOTICE '"%"' , _query;
  -- execute and return all
  RETURN QUERY EXECUTE _query;
end;
$body$;

edited Sep 05 '20 at 18:37

answered Sep 05 '20 at 18:30

Игорь Тыра

905
4
12

Great! Thanks, exactly what i needed :) – A gupta Sep 07 '20 at 10:20
Just a follow up question. When I am calling above function copy(select table_name, name_list(table_schema, table_name) from information_schema.tables) to 'C:\test\name_list.csv' DELIMITER ',' CSV HEADER; then this fails each time the column "names" is not present in particular table. One way I overcame it by using " where table_name not in()" but it is quite problematic at a time to find all the tables where that particular column is not present Can we use IF EXIST? Can you suggest how to tackle this?. – A gupta Sep 07 '20 at 13:51
you can find information about tables and columns in ```information_schema.columns``` and use ```WHERE EXISTS(SELECT * FROM information_schema.columns WHERE ...)``` – Игорь Тыра Sep 08 '20 at 17:08
Hey, Will you please see this?: https://stackoverflow.com/questions/63825174/how-to-filter-table-in-dynamic-postgresql – A gupta Sep 15 '20 at 06:33

score 1 · Answer 2 · answered Sep 06 '20 at 21:45

information_schema.tables may not give you what you expect exactly. The manual:

The view tables contains all tables and views defined in the current database. Only those tables and views are shown that the current user has access to (by way of being the owner or having some privilege).

See:

How to check if a table exists in a given schema

This query might do what you are after:

SELECT quote_ident(n.nspname) AS schema  -- identifiers quoted where necessary
     , quote_ident(c.relname) AS table
     , (SELECT string_agg(quote_ident(a.attname), ', ' ORDER BY a.attnum) AS column_list
        FROM   pg_catalog.pg_attribute a
        WHERE  a.attrelid = c.oid
        AND    a.attnum > 0
        AND    NOT a.attisdropped) AS column_list  -- all columns in physical order
FROM   pg_catalog.pg_class c
JOIN   pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE  n.nspname = 'public'  -- only public schema (?)
AND    c.relkind = 'r'       -- only plain tables (?)
ORDER  BY n.nspname, c.relname;

Result like:

schema | table           | column_list
------ +-----------------+---------------------------------------------
public | foo             | id, bar
public | spatial_ref_sys | srid, auth_name, auth_srid, srtext, proj4text
public | "weird TaBlE"   | "ID", "user", "dumb name"

db<>fiddle here

This only returns plain tables. There may be more kinds you want to include. The manual about pg_class.relkind:

r = ordinary table, i = index, S = sequence, t = TOAST table, v = view, m = materialized view, c = composite type, f = foreign table, p = partitioned table, I = partitioned index

PosgtreSQL procedure

2 Answers2