Use of Setof Record Dynamically

From: A E <cooljoint(at)yahoo(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Use of Setof Record Dynamically
Date: 2003-12-25 00:17:14
Message-ID: 20031225001714.78113.qmail@web12104.mail.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Hi,

I am attempting to use Setof Record dynamically. I am developing an application where I will not know what the end-user is trying to work with from a web front end. Thus I will not know before hand the structure of the table I am trying to pull the record from.

My Code:

CREATE FUNCTION retrievecollection(varchar, varchar, varchar) RETURNS record AS'
Declare
tablealias ALIAS FOR $1;
crmid ALIAS FOR $2;
username ALIAs FOR $3;
allowed integer;
objectdefinition record;
realtablename char;
begin
select into allowed secverf(username, tablealias);
if allowed = 0 then
RAISE NOTICE ''User not authorized to perform retrieve.'', allowed;
RETURN false;
else
select into realtablename tablename from applicationaliases where tablealias = qoute_literal(tablealias);
if length(crmid) = 0 then
FOR objectdefinition IN SELECT * from qoute_ident(realtablename) LOOP
RETURN NEXT objectdefinition;
END LOOP;
else
FOR objectdefinition IN SELECT * from qoute_ident(realtablename) where crmid = qoute_literal(crmid) LOOP
RETURN NEXT objectdefinition;
END LOOP;
end if;
end if;
RETURN record;
end;'
LANGUAGE 'plpgsql' VOLATILE;

I know that I would execute the procedure with select * from retrievecollection as x(x typex, y typey) but how do I do this if I don't know which table was passed to the function?

TIA

Alex Erwin

Browse pgsql-sql by date

  From Date Subject
Next Message Iain 2003-12-25 01:50:18 not in vs not exists - vastly diferent performance
Previous Message alvaro 2003-12-24 16:47:53 MD5 encrypt