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(at)postgresql(dot)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/