X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 06A99D1B4B3 for ; Thu, 25 Dec 2003 00:18:02 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 05879-10 for ; Wed, 24 Dec 2003 20:17:14 -0400 (AST) Received: from web12104.mail.yahoo.com (web12104.mail.yahoo.com [216.136.172.24]) by svr1.postgresql.org (Postfix) with SMTP id 6C6BED1B46C for ; Wed, 24 Dec 2003 20:17:11 -0400 (AST) Message-ID: <20031225001714.78113.qmail@web12104.mail.yahoo.com> Received: from [66.3.224.140] by web12104.mail.yahoo.com via HTTP; Wed, 24 Dec 2003 16:17:14 PST Date: Wed, 24 Dec 2003 16:17:14 -0800 (PST) From: A E Subject: Use of Setof Record Dynamically To: pgsql-sql@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-109192649-1072311434=:75542" X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=1.1 tagged_above=0.0 required=5.0 tests=BAYES_30, HTML_10_20, RCVD_IN_NJABL X-Spam-Level: * X-Archive-Number: 200312/170 X-Sequence-Number: 16336 --0-109192649-1072311434=:75542 Content-Type: text/plain; charset=us-ascii 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 --0-109192649-1072311434=:75542 Content-Type: text/html; charset=us-ascii
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

--0-109192649-1072311434=:75542--