OK here is an example of a function where I had to create a type called
login.
How could I have written this function without having to create a type.
CREATE OR REPLACE FUNCTION "public"."loginbyindidget" (integer) RETURNS
SETOF "public"."login" AS'
declare
iindid alias for $1;
returnRec RECORD;
begin
for returnRec in select tblindividual.indid, tblindividual.title,
tblindividual.firstname, tblindividual.middlename,
tblindividual.lastname, tblindividual.suffix, tblloginname.loginname,
tblloginname.loginnameid, tblloginname.ad,tblloginname.current,
tblloginname.email, tblloginname.note
from tblindividual inner join tblloginname on (tblindividual.indid =
tblloginname.indlink)
where tblloginname.indlink = iindid
order by tblindividual.lastname, tblindividual.firstname,
tblindividual.middlename, tblloginname.loginname
loop
return next returnRec;
end loop;
return;
end;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
On Wed, 2005-03-16 at 13:51 +0000, Richard Huxton wrote:
> Jason Tesser wrote:
> > 1. i have a few funcions that depend on a type. i don't want to have to srop every function just so I can drop the
typeand recreat everything.
> > Is there a better way to do this in Postgres?
>
> Not really - if you're redefining the type then the functions really
> have to be recreated. I try to keep related objects in the same file, so
> I can re-run them all together.
>
> > 2. The reason I had to create my own type was because record didn't ork for me when I was selecting data across
multipletables.
> > I thought it should be dynamic but it only seems to work if i select all data in one table. I need 2-3 columns
frommultiple
> > tables.
> > Is there a better way to do this in Postgres?
>
> Could you give more details of what you're trying? RECORD variables in
> functions should work fine.
>
> --
> Richard Huxton
> Archonet Ltd