Me gustaría usar una función/procedimiento para agregar a una tabla de 'plantilla' una columna adicional (por ejemplo, nombre de período) con múltiples valores, y hacer un producto cartesiano en las filas, por lo que mi 'plantilla' se duplica con los diferentes valores proporcionados para la nueva columna.
Por ejemplo, agregue una columna de período con 2 valores a mi tabla template_country_channel :
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 ) -- */Esta consulta funciona bien, pero me preguntaba si podría convertir eso en una función/procedimiento PL/pgSQL, proporcionando los nuevos valores de columna para agregar, la columna para agregar la columna adicional (y opcionalmente especificar el orden por condiciones) .
Lo que me gustaría hacer es:
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) );y tiene el mismo resultado que la primera consulta.
Así que creé una función como:
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' ;Pero cuando corro:
SELECT * FROM template_with_periods('template_country_channel', ARRAY['P1', 'P2'], 'period DESC, sort_cnty, sort_chan'); Tengo el error ERROR: 42601: a column definition list is required for functions returning “record”
Después de buscar en Google, parece que necesito definir la lista de columnas y tipos para realizar la RETURN QUERY (como indica precisamente el mensaje de error). Desafortunadamente, la idea general es usar la función con muchas tablas de 'plantilla', por lo que las listas de nombres y tipos de columnas no son fijas.
template ?Hice esto con refcursor si desea que la lista de columnas de salida sea completamente dinámica:
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; Luego necesita llamar FETCH ALL IN para obtener resultados. Sintaxis detallada aquí o allá: https://stackoverflow.com/a/12483222/630169 . Parece que es la única manera por ahora. Espero que algo cambie en PostgreSQL 11 con PROCEDIMIENTOS.