Postgres Pro
Downloads
Home   >   mailing lists

Thread: Returning with a userd defined type (PL/pgSQL)

Returning with a userd defined type (PL/pgSQL)

From
Együd Csaba
Date:
Hello,

I would like to return a set with a self-defined type.
Actually this type contains a subset of a physical table's columns and some
other columns.
It should hold rows which are filled in with the table data on the one hand
and the additionally calculated data on the other hand.

I can do it when I want to return a set provided by a query (using return
next;), but how can I extend this structure with additional information?

Thank you in advance.

Best Regards
Csaba

Re: Returning with a userd defined type (PL/pgSQL)

From
Joe Conway
Date:
Együd Csaba wrote:
> I can do it when I want to return a set provided by a query (using return
> next;), but how can I extend this structure with additional information?

It isn't clear (at least to me) what you are asking. Can you provide a
self-contained example function along with supporting information (table
definitions, etc.) and the error message you are getting?

Joe

Help with a query

From
"jose antonio leo"
Date:
Hi guys!

I need make a join query.
I have two tables with the next fields:

aec_cua_man:
ac1-ac2-ac3
1-1-1
1-1-2
1-1-4
1-2-2
....

aecoc:
ac1-ac2-ac3-description
1-0-0-des1
1-1-0-des2
1-1-1-des3
1-1-2-des4
1-1-3-des5
1-1-4-des6
1-1-5-des7
1-2-0-des8
1-2-1-des9
1-2-2-des10
....

I need the record of the first table with the fields ac1, ac2 and ac3 equals
than second table but in addition the fiels of the second table with a nul
in the fiels ac2 and ac3.

the table result will be:
1-0-0-des1
1-1-0-des2
1-1-1-des3
1-1-2-des4
1-1-4-des6
1-2-0-des8
1-2-2-des10

Could you help me?


Jose Antonio

Re: Returning with a userd defined type (PL/pgSQL)

From
Együd Csaba
Date:
> It isn't clear (at least to me) what you are asking. Can you provide a
> self-contained example function along with supporting information (table
> definitions, etc.) and the error message you are getting?

Hi Joe,

Sorry, I should have provided these information before.
I want to filter the rows of a table and give some additional information
calculated for every row.
(I know that I do something wrong but I couldn'n find anything in the
documentation similar to this.)

An example: (on Postgres 7.3.2, RedHat 7.1)
----------------------------------------------------------------------------
-----------------------
create table t (id      integer, name char(32));

create type MY_TYPE as (tid int, tname char(32), additional_info int);

create or replace function "my_func" () returns setof MY_TYPE as'
declare
  R            record;
  ResultR   MY_TYPE;
begin
  for R in execute ''select * from t where id > 20'' loop
    -- I know it has no meaning, but it can demonstrate what I want: simply
    -- extending the information retrieved from the table.
    ResultR.tid := R.id;
    ResultR.tname := R.name;
    ResultR.additional_info := R.id * 2;

    return next ResultR;
  end loop;
  return;
end;
'LANGUAGE 'plpgsql';

pg732=# insert into t values (1,'name1');
INSERT 39602 1
pg732=# insert into t values (2,'name2');
INSERT 39603 1
pg732=# insert into t values (20,'name20');
INSERT 39604 1
pg732=# insert into t values (21,'name21');
INSERT 39605 1
pg732=# insert into t values (22,'name22');
INSERT 39606 1
pg732=# select * from my_func();
WARNING:  plpgsql: ERROR during compile of my_func near line 12
ERROR:  Incorrect argument to RETURN NEXT at or near "ResultR"
----------------------------------------------------------------------------
-------------------

The last command should result in something similar:

 id |               name               | additional_info
----+----------------------------------+-----------------
 21 | name21                           |              42
 22 | name22                           |              44
(2 rows)

Thanks

Csaba

Re: Help with a query

From
Richard Huxton
Date:
On Friday 25 Apr 2003 8:41 am, jose antonio leo wrote:
> aec_cua_man:
> ac1-ac2-ac3
> 1-1-1
> 1-1-2
> 1-1-4
> 1-2-2
> ....
>
> aecoc:
> ac1-ac2-ac3-description
> 1-0-0-des1
> 1-1-0-des2
> 1-1-1-des3
> 1-1-2-des4
> 1-1-3-des5
> 1-1-4-des6
> 1-1-5-des7
> 1-2-0-des8
> 1-2-1-des9
> 1-2-2-des10
> ....
>
> I need the record of the first table with the fields ac1, ac2 and ac3
> equals than second table but in addition the fiels of the second table with
> a nul in the fiels ac2 and ac3.
>
> the table result will be:
> 1-0-0-des1
> 1-1-0-des2
> 1-1-1-des3
> 1-1-2-des4
> 1-1-4-des6
> 1-2-0-des8
> 1-2-2-des10
>
> Could you help me?

Simplest method might be a union - do the join on the 3 fields and union it
with a simple select on aecoc where ac3=0 or ac2=0. Syntax is something like:

SELECT ac1,ac2,ac3,description FROM .... (join here)
UNION
SELECT ac1,ac2,ac3,description FROM aecoc... (grab zero descriptions here)

Note that the order of the result columns should be the same in both cases. If
you know there will be no zeros in aec_cua_man then you might want to use
UNION ALL.

See the User's guide Ch4 or the SQL SELECT reference for some details.

--
  Richard Huxton

Re: Returning with a userd defined type (PL/pgSQL)

From
Joe Conway
Date:
Együd Csaba wrote:
>   ResultR   MY_TYPE;

Not the best of error messages perhaps, but change the above line to:

   ResultR   MY_TYPE%ROWTYPE;

HTH,

Joe

Re: Returning with a userd defined type (PL/pgSQL)

From
"Egyud Csaba"
Date:
Joe,

thank you for your help. It was really simple.

-- Csaba

----- Original Message -----
From: "Joe Conway" <mail@joeconway.com>
To: "Együd Csaba" <csegyud@freemail.hu>
Cc: "pgsql-general" <pgsql-general@postgresql.org>
Sent: Friday, April 25, 2003 7:35 AM
Subject: Re: [GENERAL] Returning with a userd defined type (PL/pgSQL)


> Együd Csaba wrote:
> >   ResultR   MY_TYPE;
>
> Not the best of error messages perhaps, but change the above line to:
>
>    ResultR   MY_TYPE%ROWTYPE;
>
> HTH,
>
> Joe
>
>
>
>


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.435 / Virus Database: 244 - Release Date: 2002.12.30.