Re: Fetching column names for a table

On 9/21/05, Steve Manes <smanes@magpie.com> 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';