Обсуждение: Problems with stored procedure (function)

Hello pgsql-novice,

I'm trying to return a set of rows from a stored procedure, but
haven't quite hit on the right syntax. I'm sure this is quite a
trivial question, but all my googling has failed me (and so has
"PostgreSQL: The comprehensive guide to building, programming, and
administering PostgreSQL databases, Second Edition").

My code is:

create table country (
   name varchar(100),
   alpha_two_code char(2),
   alpha_three_code char(3),
   numeric_code int,
   primary key (numeric_code)
);

create or replace function load_country() returns setof country as
$body$
begin
   return query select * from country;
   return;
end
$body$
language 'plpgsql';

The problem I have is my function keeps on returning a single column
with the name of the function, rather than something akin to the table
I've defined:

sanzala=# insert into country values('a','b','c',123);
INSERT 0 1

sanzala=# select * from country;
 name | alpha_two_code | alpha_three_code | numeric_code
------+----------------+------------------+--------------
 a    | b              | c                |          123
(1 row)

sanzala=# select load_country();
    load_country
--------------------
 (a,"b ","c  ",123)
(1 row)

I guess the fault is in "setof country", but I've tried returning
_country, country%rowtype, etc - all with no luck. What am I doing
wrong?

Many thanks for your time,

Marco
--
It's the golden rule: those who have the gold, rule. -- Gerald Celente

Marco Craveiro <marco.craveiro@gmail.com> writes:
> The problem I have is my function keeps on returning a single column
> with the name of the function, rather than something akin to the table
> I've defined:

> sanzala=# select load_country();
>     load_country
> --------------------
>  (a,"b ","c  ",123)
> (1 row)

Yes, because that's what that syntax says to do: return one
composite-type column.  The easiest way to expand it is

    select * from load_country();

            regards, tom lane

Tom,

This is indeed the answer to my problem, thanks very much. Any newbie
googling for an answer to this question may also be interested in the
following series of articles:

QUICK GUIDE TO WRITING PLPGSQL FUNCTIONS:

Part 1:
http://www.postgresonline.com/journal/index.php?/archives/58-Quick-Guide-to-writing-PLPGSQL-Functions-Part-1.html
Part 2:
http://www.postgresonline.com/journal/index.php?/archives/76-Quick-Guide-to-writing-PLPGSQL-Functions-Part-2.html
Part 3:
http://www.postgresonline.com/journal/index.php?/archives/83-Quick-Guide-to-writing-PLPGSQL-Functions-Part-3-NOTICES,-RECURSION,-and-more.html

Thanks

Marco

On 18/12/2009, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Marco Craveiro <marco.craveiro@gmail.com> writes:
>> The problem I have is my function keeps on returning a single column
>> with the name of the function, rather than something akin to the table
>> I've defined:
>
>> sanzala=# select load_country();
>>     load_country
>> --------------------
>>  (a,"b ","c  ",123)
>> (1 row)
>
> Yes, because that's what that syntax says to do: return one
> composite-type column.  The easiest way to expand it is
>
>     select * from load_country();
>
>             regards, tom lane
>


--
It's the golden rule: those who have the gold, rule. -- Gerald Celente