Calling SQL functions from ECPG - Mailing list pgsql-interfaces

Sorry for the long note, but I've included all the sample code I've been
playing with to get this to work in order to be as complete as possible.

I'm working on an application using PostgreSQL 7.3.6 which has a web-based
component and a C interface. The web side accesses the database through PHP,
and I'm trying to use ECPG on the C side. In order to keep things
maintainable, I'm trying to use a set of functions to return rows of the
tables to both PHP and ECPG, but this doesn't seem to be working.

Here's the sample SQL code I've been working with:

CREATE TYPE foo_type AS (id INTEGER,data TEXT
);

CREATE TABLE foo (id INTEGER,data TEXT
);

CREATE FUNCTION get_foo(INTEGER) RETURNS SETOF foo AS 'SELECT * FROM foo WHERE id = $1;
' LANGUAGE SQL;

INSERT INTO foo VALUES (1, 'This is the first element');
INSERT INTO foo VALUES (2, 'This is the second element');


And here's the sample ECPG code:

#include <stdio.h>
#include <ecpgerrno.h>

typedef struct { int id; char *data;
} my_type, *my_type_ptr;

EXEC SQL TYPE pg_my_type IS STRUCT
{ int id; char *data;
};

EXEC SQL TYPE my_type IS pg_my_type;
EXEC SQL TYPE my_type_ptr IS pg_my_type REFERENCE;

main() { EXEC SQL BEGIN DECLARE SECTION; my_type record = { 0 }; EXEC SQL END DECLARE SECTION;
 EXEC SQL CONNECT TO postgres USER postgres;
 EXEC SQL SELECT * INTO :record FROM foo WHERE id = 1;
 if (sqlca.sqlcode != 0) {   fprintf (stderr, "Error is \"%s\"\n", sqlca.sqlerrm.sqlerrmc); }
 EXEC SQL COMMIT;
 if (sqlca.sqlcode != 0) {   fprintf (stderr, "Error is \"%s\"\n", sqlca.sqlerrm.sqlerrmc); } else {   fprintf (stdout,
"Recordcontents is \"%s\"\n", record.data); }
 
}

Obviously this isn't using a function. However, this code *does* work. When
I convert the SELECT to:

EXEC SQL SELECT get_foo(1) INTO :record;

I get a "ERROR: Cannot display record of type RECORD at line 26".

I've also tried

EXEC SQL SELECT get_foo(1) AS my_type INTO :record;

As well as changing the return type of get_foo() to SETOF foo_type. Nothing
seems to work.

Now, the archives and the documents hint to the fact that it's possible to
do what I want, but I can't find any examples that actually work. So the
question is: what am I doing wrong?

TIA,

Steve.

This e-mail message, including any attachments, is for the sole use
of the intended recipient(s) and may contain confidential information.  Any
unauthorized review, use, disclosure or distribution is prohibited.  If you
are not the intended recipient(s) please contact the sender by reply e-mail
and destroy all copies of the original message. Finally, the recipient
should check that this email is authentic and examine it for the presence of
viruses. Xerox does perform virus checks but cannot accept liability for any
damage caused by any virus transmitted by this email. Thank you.