From: | Jason Tesser <jtesser(at)nbbc(dot)edu> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: 2 questions about types |
Date: | 2005-03-16 14:18:22 |
Message-ID: | 1110982702.5882.3.camel@075a-03.nmi.northlandministries.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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 type and 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 multiple tables.
> > 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 from multiple
> > 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
From | Date | Subject | |
---|---|---|---|
Next Message | Doug McNaught | 2005-03-16 14:30:30 | Re: prelimiary performance comparison pgsql vs mysql |
Previous Message | FERREIRA William (COFRAMI) | 2005-03-16 14:17:57 | plPerl subroutine |