1

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.

1
  • 1
    Probably needs RETURN QUERY EXECUTE ... Dec 18, 2017 at 23:11

1 Answer 1

1

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

3
  • That is elegant. I was thinking about a UNION but didn't have the structure that you have. However, I didn't make it clear enough that I have a large and dynamic set of tables that I'm working with, so pre-compiling the list is not the way to go. Is there a way to build up the UNION ALL approach with table_names coming from the appropriate SELECT? Dec 19, 2017 at 1:00
  • @ShawnMehan that's what this does, every time you SELECT * FROM foo() you get a new query. Dec 19, 2017 at 1:08
  • 1
    This isn't working for me for my query, but that could be other issues, and I see the art in the structure. Thanks for the enlightenment. Dec 19, 2017 at 3:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.