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.
RETURN QUERY EXECUTE ...