Re: Fetching column names for a table

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/