I need to create a function, which returns results of a SELECT query. This SELECT query is a JOIN of few temporary tables created inside this function. Is there any way to create such function? Here is an example (it is very simplified, in reality there are multiple temp tables with long queries):
CREATE OR REPLACE FUNCTION myfunction () RETURNS TABLE (column_a TEXT, column_b TEXT) AS $$
BEGIN
CREATE TEMPORARY TABLE raw_data ON COMMIT DROP
AS
SELECT d.column_a, d2.column_b FROM dummy_data d JOIN dummy_data_2 d2 using (id);
RETURN QUERY (select distinct column_a, column_b from raw_data limit 100);
END;
$$
LANGUAGE 'plpgsql' SECURITY DEFINER
I get error:
[Error] Script lines: 1-19 -------------------------
ERROR: RETURN cannot have a parameter in function returning set;
use RETURN NEXT at or near "QUERY"Position: 237
I apologize in advance for any obvious mistakes, I'm new to this.
Psql version is PostgreSQL 8.2.15 (Greenplum Database 4.3.12.0 build 1)
Copyright Notice:Content Author:「postgresql_beginner」,Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article:https://stackoverflow.com/questions/46389505/create-function-with-temporary-tables-that-return-a-select-query-using-these-tem