I’ve modified the version above to run for all tables and support new SQL 2005 data types. It also retains the primary key names. Works only on SQL 2005 (using cross apply). select ‘create table [‘ + so.name + ‘] (‘ + o.list + ‘)’ + CASE WHEN tc.Constraint_Name IS NULL THEN ” ELSE ‘ALTER … Read more
Dynamic SQL and RETURN type (I saved the best for last, keep reading!) You want to execute dynamic SQL. In principal, that’s simple in plpgsql with the help of EXECUTE. You don’t need a cursor. In fact, most of the time you are better off without explicit cursors. The problem you run into: you want … Read more
This can be further simplified and improved: CREATE OR REPLACE FUNCTION some_f(_tbl regclass, OUT result integer) LANGUAGE plpgsql AS $func$ BEGIN EXECUTE format(‘SELECT (EXISTS (SELECT FROM %s WHERE id = 1))::int’, _tbl) INTO result; END $func$; Call with schema-qualified name (see below): SELECT some_f(‘myschema.mytable’); — would fail with quote_ident() Or: SELECT some_f(‘”my very uncommon table … Read more