| 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: | Whole Thread | Raw Message | 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
| 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 |