[SQL] Returning Setof Record Dynamically
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
[SQL] Use of Setof Record Dynamically
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
[SQL] REPOST[GENERAL] Quoting for a Select Into - Please Help
Hi, Could someone help me with quoting this right? select into aliasvalue ''|| trim(arrayval[i]) ||'' from ''|| trim(realname) ||'' where ''|| trim(searchfield) ||'' like %''|| trim(searchvalue) ||''%; The parser does not seem to want to put the value of the variables into the statement. Alex
