Re: Fetching column names for a table

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!