Skip site navigation (1) Skip section navigation (2)

Problems with stored procedure (function)

From: Marco Craveiro <marco(dot)craveiro(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Problems with stored procedure (function)
Date: 2009-12-18 19:22:28
Message-ID: d23da9a0912181122q41dc5a51t9925fa39c22aee42@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
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

Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2009-12-18 19:33:20
Subject: Re: Problems with stored procedure (function)
Previous:From: Sean DavisDate: 2009-12-18 14:06:26
Subject: Re: Conditionally executing multiple statements in series as single SQL statement

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group