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 05D9DD1B439 for ; Thu, 18 Dec 2003 18:21:07 +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 63964-01 for ; Thu, 18 Dec 2003 14:20:17 -0400 (AST) Received: from web12107.mail.yahoo.com (web12107.mail.yahoo.com [216.136.172.27]) by svr1.postgresql.org (Postfix) with SMTP id 24569D1B906 for ; Thu, 18 Dec 2003 14:20:16 -0400 (AST) Message-ID: <20031218182016.53777.qmail@web12107.mail.yahoo.com> Received: from [208.54.141.106] by web12107.mail.yahoo.com via HTTP; Thu, 18 Dec 2003 10:20:16 PST Date: Thu, 18 Dec 2003 10:20:16 -0800 (PST) From: A E Subject: Returning Setof Record Dynamically To: pgsql-sql@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-590811682-1071771616=:53579" X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.3 tagged_above=0.0 required=5.0 tests=BAYES_30, HTML_10_20 X-Spam-Level: X-Archive-Number: 200312/148 X-Sequence-Number: 16314 --0-590811682-1071771616=:53579 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-590811682-1071771616=:53579 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-590811682-1071771616=:53579--