From: Alexey Nalbat <alexey@price.ru>
Reply-To: alexey@price.ru
Organization: Price Express
To: pgsql-interfaces@postgresql.org
Subject: can external C-function get multiple rows?
Date: Mon, 23 Apr 2001 19:35:22 +0400
Content-Type: text/plain;
  charset="koi8-r"
MIME-Version: 1.0
Message-Id: <01042320014100.09239@workshop.price.ru>
Content-Transfer-Encoding: 8bit

I want my external C-function 'triple(int4)' to return multiple rows as 'SETOF int4' in order to be used in the sql-statement
'select a.plno,a.prod from ( select * from plprice_00_1 ) a, ( select triple(17366760) as plno ) b where a.plno=b.plno'.

Table 'plprice_00_1' is

pl=# \d plprice_00_1 
         Table "plprice_00_1"
 Attribute |     Type      | Modifier  
-----------+---------------+-----------
 plno      | integer       | not null
 prod      | varchar(512)  | 
Indices: i_prc_prod_00_1,
         pk_prc_plno_00_1

with primary key

pl=# \d pk_prc_plno_00_1
Index "pk_prc_plno_00_1"
 Attribute |  Type   
-----------+---------
 plno      | integer
unique btree (primary key)

I created C-function as

#include <stdlib.h>
int
triple(int i)
{
        int *n = (int *)malloc(3*sizeof(int));
        n[0] = i;
        n[1] = 2*i;
        n[2] = 3*i;
        return *n;
}

and compiledit on Solaris using

# gcc -fpic -c triple.c
# gcc -G -o triple.so triple.o

Creating function in psql made no error

# CREATE FUNCTION triple(int4) RETURNS SETOF int4 AS '/var/local/lib/pgsql/lib/triple.so' LANGUAGE 'C';
CREATE

And now it just hung up (after pressing ^C it came back with "Cancel request sent ERROR:  Query was cancelled.") when I call

# select triple(1);

So, can C-function return multiple rows? And if yes, what was my error?

Thanks in advance.

P.S.: Excuse me for bad english.

-- 

Alexey Nalbat