I would like to use a function/procedure to add to a 'template' table an additional column (e.g. period name) with multiple values, and do a cartesian product on the rows, so my 'template' is duplicated with the different values provided for the new column.
E.g. Add a period column with 2 values to my template_country_channel table:
SELECT *
FROM unnest(ARRAY['P1', 'P2']) AS prd(period)
, template_country_channel
ORDER BY period DESC
, sort_cnty
, sort_chan;
/*
-- this is equivalent to:
(
SELECT 'P2'::text AS period
, *
FROM template_country_channel
) UNION ALL (
SELECT 'P1'::text AS period
, *
FROM template_country_channel
)
--
*/
This query is working fine, but I was wondering if I could turn that into a PL/pgSQL function/procedure, providing the new column values to be added, the column to add the extra column to (and optionally specify the order by conditions).
What I would like to do is:
SELECT *
FROM template_with_periods(
'template_country_channel' -- table name
, ARRAY['P1', 'P2'] -- values for the new column to be added
, 'period DESC, sort_cnty, sort_chan' -- ORDER BY string (optional)
);
and have the same result as the 1st query.
So I created a function like:
CREATE OR REPLACE FUNCTION template_with_periods(template regclass, periods text[], order_by text)
RETURNS SETOF RECORD
AS $BODY$
BEGIN
RETURN QUERY EXECUTE 'SELECT * FROM unnest($2) AS prd(period), $1 ORDER BY $3' USING template, periods, order_by ;
END;
$BODY$
LANGUAGE 'plpgsql'
;
But when I run:
SELECT *
FROM template_with_periods('template_country_channel', ARRAY['P1', 'P2'], 'period DESC, sort_cnty, sort_chan');
I have the error ERROR: 42601: a column definition list is required for functions returning “record”
After some googling, it seems that I need to define the list of columns and types to perform the RETURN QUERY (as the error message precisely states).
Unfortunately, the whole idea is to use the function with many 'template' tables, so columns name & type lists is not fixed.
template table?I did this with refcursor if You want output columns list completely dynamic:
CREATE OR REPLACE FUNCTION is_record_exists(tablename character varying, columns character varying[], keepcolumns character varying[] DEFAULT NULL::character varying[])
RETURNS SETOF refcursor AS
$BODY$
DECLARE
ref refcursor;
keepColumnsList text;
columnsList text;
valuesList text;
existQuery text;
keepQuery text;
BEGIN
IF keepcolumns IS NOT NULL AND array_length(keepColumns, 1) > 0 THEN
keepColumnsList := array_to_string(keepColumns, ', ');
ELSE
keepColumnsList := 'COUNT(*)';
END IF;
columnsList := (SELECT array_to_string(array_agg(name || ' = ' || value), ' OR ') FROM
(SELECT unnest(columns[1:1]) AS name, unnest(columns[2:2]) AS value) pair);
existQuery := 'SELECT ' || keepColumnsList || ' FROM ' || tableName || ' WHERE ' || columnsList;
RAISE NOTICE 'Exist query: %', existQuery;
OPEN ref FOR EXECUTE
existQuery;
RETURN next ref;
END;$BODY$
LANGUAGE plpgsql;
Then need to call FETCH ALL IN to get results. Detailed syntax here or there: https://stackoverflow.com/a/12483222/630169. Seems it is the only way for now. Hope something will be changed in PostgreSQL 11 with PROCEDURES.