Re: display table contents using a stored proc

You don't want it to return void. Also for something this simple, you really
don't need to use plpgsql. You can just use sql language

Try something like

create or replace function show_table (
cidr -- ip_block
) returns setof ip_table
language sql as '
select * from ip_table
where ip_block = $1;
';

If you are using it to return a table structure then you'll probably want to
do something like

select * from show_table('62.51.0.0/16');

-----Original Message-----
From: Manish Raj Sharma [mailto:manishrs(at)aol(dot)net]
Sent: Wednesday, October 05, 2005 8:16 AM
To: pgsql-novice(at)postgresql(dot)org
Subject: [NOVICE] display table contents using a stored proc

Hi,

I am new to stored procedures and have been trying to display the
contents of a table using a stored proc as follows:

create or replace function show_table (
cidr -- ip_block
) returns void
language plpgsql as '
declare
block alias for $1;
begin
select * from ip_table
where ip_block = block
return;
end;
';

When I call the function, I get the following:

mydb=# SELECT show_table('62.51.0.0/16');
ERROR: SELECT query has no destination for result data
HINT: If you want to discard the results, use PERFORM instead.
CONTEXT: PL/pgSQL function "show_table" line 4 at SQL statement

Any help?

Thanks,
-manish

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match