Thread: Fetching column names for a table

I need to extract a SETOF column names for a table in plpgsql.  How is
this done?

---------------------------------------------=o&o>---------
Steve Manes          http://www.magpie.com
Brooklyn, NY

> I need to extract a SETOF column names for a table in plpgsql.  How is this
> done?

Start up psql with the -E option.  Then type "\dt tablename".  This will
print out the SQL that psql runs to give you the column names.  Maybe that
will do what you want?

-philip

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';

On Wed, Sep 21, 2005 at 02:31:23PM -0400, Steve Manes wrote:
> I need to extract a SETOF column names for a table in plpgsql.  How
> is this done?

You can do it in SQL.

CREATE OR REPLACE FUNCTION get_columns_for (
    in_schema TEXT,
    in_table TEXT
) RETURNS SETOF TEXT
LANGUAGE SQL
STRICT
AS $$
SELECT c.column_name
FROM information_schema.columns c
WHERE c.table_schema = $1
AND c.table_name = $2
ORDER BY ordinal_position;
$$;

CREATE OR REPLACE FUNCTION get_columns_for (
    in_table TEXT
) RETURNS SETOF TEXT
LANGUAGE SQL
STRICT
AS $$
SELECT * FROM get_columns_for('public', $1);
$$;

HTH :)

Cheers,
D
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!

Steve Manes wrote:

> I need to extract a SETOF column names for a table in plpgsql.  How is
> this done?

A query such as this:

 select * from information_schema.columns where table_name = 'table_name';

Will give you a bunch of information. For SET OF functions in general
take a look at:

http://techdocs.postgresql.org/guides/SetReturningFunctions

You can also use something like this:

CREATE TYPE column_type_set AS (column_name text, column_type text);
CREATE OR REPLACE FUNCTION describe_table (text, text) RETURNS SETOF
column_type_set AS '
  SELECT attname::text, typname::text
    FROM pg_namespace, pg_attribute, pg_type, pg_class
    WHERE pg_type.oid = atttypid
      AND pg_class.oid = attrelid
      AND relname = $2 AND attnum >= 1
      AND relnamespace = pg_namespace.oid
      AND pg_namespace.nspname = $1;
' LANGUAGE 'SQL';



Sincerely,

Joshua D. Drake


>
> ---------------------------------------------=o&o>---------
> Steve Manes          http://www.magpie.com
> Brooklyn, NY
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly



--
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/