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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s