Thread: 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

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@aol.net]
Sent: Wednesday, October 05, 2005 8:16 AM
To: pgsql-novice@postgresql.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