Thread: 2 questions about types
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? 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 from multiple tables. Is there a better way to do this in Postgres? I am using Suse with Postgres 7.4.2 but am considering an upgrade to 8.0 Thank you, Jason Tesser
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 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
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
Jason Tesser wrote:
> 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'
[snip]
There's an example in the manuals - chapter "7.2.1.4. Table Functions"
SELECT *
FROM dblink('dbname=mydb', 'select proname, prosrc from pg_proc')
AS t1(proname name, prosrc text)
WHERE proname LIKE 'bytea%';
So basically, you need to supply the type definitions in your SELECT if
you aren't going to supply it in the function definition.
--
Richard Huxton
Archonet Ltd
<snip>
>
> There's an example in the manuals - chapter "7.2.1.4. Table Functions"
>
> SELECT *
> FROM dblink('dbname=mydb', 'select proname, prosrc from pg_proc')
> AS t1(proname name, prosrc text)
> WHERE proname LIKE 'bytea%';
>
> So basically, you need to supply the type definitions in your SELECT if
> you aren't going to supply it in the function definition.
ok I tried to rewrite as follows but I get an error that says "a column
definition list is required fro functions returning record
here is my function and call for it now
CREATE OR REPLACE FUNCTION "public"."loginbyindidgettest" (integer)
RETURNS SETOF "pg_catalog"."record" AS'
declare
iindid alias for $1;
returnRec RECORD;
begin
for returnRec in select t1.indid, t1.title, t1.firstname, t1.middlename,
t1.lastname, t1.suffix, t1.loginname, t1.loginnameid, t1.ad,t1.current,
t1.email, t1.note
from tblindividual inner join tblloginname on (tblindividual.indid =
tblloginname.indlink) as t1
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;
select * from loginbyindidgettest(43650);
Jason Tesser wrote:
> <snip>
>
>>There's an example in the manuals - chapter "7.2.1.4. Table Functions"
>>
>>SELECT *
>> FROM dblink('dbname=mydb', 'select proname, prosrc from pg_proc')
>> AS t1(proname name, prosrc text)
>> WHERE proname LIKE 'bytea%';
>>
>>So basically, you need to supply the type definitions in your SELECT if
>>you aren't going to supply it in the function definition.
>
>
> ok I tried to rewrite as follows but I get an error that says "a column
> definition list is required fro functions returning record
Because you didn't supply the type definitions in your SELECT...
> here is my function and call for it now
> CREATE OR REPLACE FUNCTION "public"."loginbyindidgettest" (integer)
> RETURNS SETOF "pg_catalog"."record" AS'
...
> select * from loginbyindidgettest(43650);
This needs to be something like:
SELECT * FROM loginbyindidgettest(43650) AS myres(a int, b text, c
date, ...)
Obviously, the types need to match the results of your function.
--
Richard Huxton
Archonet Ltd