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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2009-12-18 19:33:20 | Re: Problems with stored procedure (function) |
Previous Message | Sean Davis | 2009-12-18 14:06:26 | Re: Conditionally executing multiple statements in series as single SQL statement |