Passing a table name to a function for dynamic queries....

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

Responses

Browse pgsql-general by date

  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