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





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'Declaretablealias ALIAS FOR $1;crmid ALIAS FOR $2;username ALIAs FOR $3;allowed integer;objectdefinition record;realtablename char;
beginselect 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
Fri, 26 Dec 2003 14:32:55 -0800 (PST)
A E <cooljoint@yahoo.com>
REPOST from SQL List: Use of Setof Record Dynamically
A E wrote:
> 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.

[code]

> 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?

The short answer is there is no way to do what you seem to want to do,
at least not directly.

You might try storing a suitable column definition string ("columndef") 
in the "applicationaliases" table for each referenced table. From your 
web page first do:

   SELECT columndef
   FROM applicationaliases
   WHERE tablealias = qoute_literal(tablealias);

Alternatively you could dynamically build the columndef based on tablename.

Then use the result to build the second query; something like:

   SELECT *
   FROM retrievecollection($tablealias, $crmid, $username)
   AS ($columndef);


HTH,

Joe


Fri, 26 Dec 2003 17:46:11 -0800
Joe Conway <mail@joeconway.com>
Re: REPOST from SQL List: Use of Setof Record Dynamically