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