Postgresql – How to return rows from multiple tables selected from information_schema

dynamic-sqlplpgsqlpostgresql

I need to return rows from a set of tables found in information_schema. I can find the table names, and I have a function that compiles and executes, but it returns an empty set, so there must be something wrong with my logic.

CREATE OR REPLACE FUNCTION get_prices() 
  RETURNS TABLE(price FLOAT) AS $func$
    DECLARE
      tn TEXT;
      BEGIN
        FOR tn in SELECT table_name 
          FROM information_schema.tables 
          WHERE table_name ~~ '%price%'  
          AND table_schema ~~ 'stg0' LOOP
            EXECUTE 'SELECT price FROM stg0.' || tn;
          END LOOP;
      END;
  $func$ LANGUAGE plpgsql;

When I execute the following

SELECT * FROM get_prices();

I get a table with no rows and a single column, price.

The SELECT in the FOR loop is returning all the needed table names. There are many rows in those tables in the price column. What should change to return those rows?

UPDATE: I should have made it more clear that I have a large, undetermined number of source tables for the query at compile time, so I was looking to query the information_schema at runtime in order to pull the source table names dynamically.

The given query works and actually returns data by replacing the EXECUTE line with

RETURN QUERY EXECUTE 'SELECT price FROM stg0.' || tn;

as suggested in the comments.

Best Answer

That's 0-fun. Just generate one statement using UNION ALL and now you don't need cursors.

CREATE TABLE foo_1(price) AS VALUES (1);
CREATE TABLE foo_2(price) AS VALUES (1);
CREATE TABLE foo_3(price) AS VALUES (1);

SELECT STRING_AGG(
  FORMAT(
    'SELECT price FROM %I.%I.%I',
    table_catalog,
    table_schema,
    table_name
  ), ' UNION ALL ') ||';'
FROM information_schema.tables
WHERE table_name like '%foo_%';

You can put it in a function like this (first we get the result as a query string, and then we execute it)

CREATE FUNCTION get_prices()
RETURNS setof int AS $func$
  DECLARE
    sql text := string_agg(
      FORMAT(
        'SELECT price::int FROM %I.%I.%I',
        table_catalog,
        table_schema,
        table_name
      ), ' UNION ALL ') ||';'
    FROM information_schema.tables
    WHERE table_name like '%foo_%';
  BEGIN
    RETURN QUERY EXECUTE sql;
  END;
$func$ LANGUAGE plpgsql;

As a special note, if the list of table_name ~~ '%price%' AND table_schema ~~ 'stg0' is immutable (that means you're not still adding to it) you're better of doing this one time and saving it as a VIEW

Related Solutions

Postgresql – How to correctly return a query result only if it isn’t NULL

I don't think you need a cursor here at all. To shorten your code, you could just use a view. To improve performance, a materialized view should get you furthest. Postgres 9.3 has built-in features, but you can easily implemented it in older versions yourself.

Consider this simplified form:

CREATE FUNCTION store_distance(_lat    double precision
                              ,_long   double precision
                              ,_radius double precision
                              ,_tries  integer)
RETURNS TABLE(
   store_id   store.id%type
  ,store_name store.name%type
  ,distance   double precision) AS
$func$
DECLARE
   _ct  int   := 0;
   _pos point := point(_lat, _long);
BEGIN
   LOOP
      EXIT WHEN _ct >= _tries
           OR   EXISTS (
            SELECT 1 FROM store s
            WHERE  point(s.latitude, s.longitude) <@ circle(_pos, _radius));

      _radius := _radius * 2;
      _ct     := _ct + 1;
   END LOOP;

   RETURN QUERY
   SELECT s.id, s.name
         ,get_distance(_lat, _long, s.latitude, s.longitude)
   FROM   store s
   WHERE  point(s.latitude, s.longitude) <@ circle(_pos, _radius);
   ORDER  BY 3;
END
$func$ LANGUAGE plpgsql STRICT;

I made the function STRICT to disallow NULL input, which could result in an endless loop.

Note how I use circles with the "Contained" operator <@ instead of a boxes. One would assume calculations to be slightly more expensive than with boxes, but it hardly matters at all once you support your query with a GiST index like:

CREATE INDEX store_point_gist_idx ON store
USING gist (point(latitude, longitude));

You might consider to store lat / lon as point to begin with and replace the index on an expression with a simpler one on the column. Works either way, just make sure the query matches the index so it gets used. Big difference for big tables.

You may be interested in this closely related answer on SO I posted last year - with a lot more explanation and links.

Postgresql – How to create function(table_name) that returns a set of the table

You cannot use a parameter name (_t) as return type.
Use polymorphic types instead:

CREATE OR REPLACE FUNCTION vsr_versioning_at_time(_t anyelement, _d timestamp)
  RETURNS SETOF anyelement AS
$func$
BEGIN
   RETURN QUERY EXECUTE format('
       SELECT DISTINCT ON (gid) * 
       FROM   %s
       WHERE  vrs_start_time <= $1
       ORDER  BY gid, vrs_start_time DESC'
     , pg_typeof(_t)
     )
   USING _d;
END
$func$  LANGUAGE plpgsql;

And use RETURN QUERY EXECUTE to actually return the results.
Call (important!):

SELECT * FROM vsr_versioning_at_time(NULL::mytable, '2014-04-18 12:00');

A lot more details in this related answer on SO (last chapter):
Refactor a PL/pgSQL function to return the output of various SELECT queries

Best Answer

Related Solutions

Postgresql – How to correctly return a query result only if it isn’t NULL

Postgresql – How to create function(table_name) that returns a set of the table

Related Question