On 9/21/05, Steve Manes <[email protected]> wrote:
> I need to extract a SETOF column names for a table in plpgsql. How is
> this done?
I got the queries for this by running psql with -E and then using \d
on a table. Use this function like so: SELECT * FROM
column_names('your_table');
CREATE OR REPLACE FUNCTION column_names(in_tablename TEXT) RETURNS
SETOF TEXT AS $BODY$
DECLARE
rec RECORD;
table_oid INTEGER;
i INTEGER := 0;
BEGIN
FOR rec IN SELECT attname
FROM pg_catalog.pg_attribute
WHERE attnum > 0 AND NOT attisdropped
AND attrelid = (
SELECT c.oid
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE pg_catalog.pg_table_is_visible(c.oid)
AND c.relname = in_tablename
)
ORDER BY attname ASC
LOOP
RETURN NEXT rec.attname;
i := i+1;
END LOOP;
IF i < 1 THEN
RAISE NOTICE'no table called % found. Verify table exists and try
prepending the schema.',in_tablename;
END IF;
RETURN;
END;
$BODY$ LANGUAGE 'plpgsql';
