pg.ddx.io  pgsql-interfaces@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Calling SQL functions from ECPG
2+ messages / 2 participants
[nested] [flat]

* Calling SQL functions from ECPG
@ 2004-04-20 16:39 Owens, Steve <Steve.Owens@usa.xerox.com>
  2004-04-21 03:50 ` Re: Calling SQL functions from ECPG Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 2+ messages in thread

From: Owens, Steve @ 2004-04-20 16:39 UTC (permalink / raw)
  To: 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, "Record contents 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.





^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Calling SQL functions from ECPG
  2004-04-20 16:39 Calling SQL functions from ECPG Owens, Steve <Steve.Owens@usa.xerox.com>
@ 2004-04-21 03:50 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2004-04-21 03:50 UTC (permalink / raw)
  To: Owens, Steve <Steve.Owens@usa.xerox.com>; +Cc: pgsql-interfaces

"Owens, Steve" <Steve.Owens@usa.xerox.com> writes:
> EXEC SQL SELECT get_foo(1) INTO :record;

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

I know zip about ECPG, but I'd think a more likely bet is

	EXEC SQL SELECT * INTO :record FROM get_foo(1);
or
	EXEC SQL SELECT * FROM get_foo(1) INTO :record;

depending on what your theology is about the placement of INTO.  The
point is that a function returning setof-record is a table source and
should appear in FROM.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2004-04-21 03:50 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-04-20 16:39 Calling SQL functions from ECPG Owens, Steve <Steve.Owens@usa.xerox.com>
2004-04-21 03:50 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox