From: | "Greg Patnude" <gpatnude(at)hotmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Passing a table name to a function for dynamic queries.... |
Date: | 2005-06-29 17:25:15 |
Message-ID: | d9ul70$2ca1$1@news.hub.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I know I've seen the answer to this here before but cannot seem to find
it.... Can anyone give me the quick & dirty answer to passing a table name
to a function like this:
CREATE OR REPLACE FUNCTION tbl_toarray(name) RETURNS text
$BODY$
DECLARE ROW RECORD;
BEGIN
SELECT ARRAY(SELECT ''id:'' || id || '', type_desc:'' || type_desc FROM
$1) INTO ROW;
RETURN NEXT ROW;
END;
$BODY$
LANGUAGE PLPGSQL;
I'd like to be able to pass the function a table name and have the contents
of the table returned as an array of text....
THIS WORKS:
dmconfig=# SELECT ARRAY(SELECT '[id:' || id || ', type_desc:' || type_desc
|| ']' FROM lu_user_type WHERE active_flag);
?column?
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{"[id:1, type_desc:System]","[id:2, type_desc:Company]","[id:3,
type_desc:Administrator]","[id:4, type_desc:Employee]","[id:5,
type_desc:User (standalone)]","[id:6, type_desc:Guest / Demo]"}
(1 row)
TIA....
Regards,
Greg Patnude - Manager, Dynamic Applications Group
Data Mosaics, Inc.
2406 South Dishman-Mica Road / Suite # 6
Spokane Valley, WA 99206-6429
VOICE: (866) 904-DMSF
FAX: (509) 928-4236
From | Date | Subject | |
---|---|---|---|
Next Message | Jeffrey Melloy | 2005-06-29 18:20:01 | Re: Need help writing SQL statement |
Previous Message | Sven Willenberger | 2005-06-29 16:22:36 | Re: PostgreSQL's vacuumdb fails to allocate memory for |