Tag Archives: function

Function to generate insert statements from table (PostgreSQL)

This function is useful to return insert statements from a table:

CREATE OR REPLACE FUNCTION public.generate_inserts(varSchema text, varTable text) RETURNS TABLE(resultado text) AS $$

DECLARE CODE TEXT;

BEGIN
CODE :=
(SELECT
'SELECT ''INSERT INTO '
|| table_schema || '.'
|| table_name ||' ('
|| replace(replace(array_agg(column_name::text)::text,'{',''),'}','') || ') VALUES (''||'
|| replace(replace(replace(array_agg('quote_nullable(' || column_name::text || ')')::text,'{',''),'}',''),',',' || '','' || ')
|| ' || '');'' '
|| 'FROM ' || table_schema || '.' || table_name || ';'
FROM information_schema.columns
WHERE table_schema = varSchema
AND table_name = varTable
GROUP BY table_schema, table_name);

RETURN QUERY
EXECUTE CODE;
END;
$$ LANGUAGE plpgsql;

Calling the function:

SELECT public.generate_inserts('YourSchema','YourTable');

Advertisements