pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDo not understand "SETOF RECORD" - therefore can not use ODBC-link
3+ messages / 2 participants
[nested] [flat]
* Do not understand "SETOF RECORD" - therefore can not use ODBC-link
@ 2010-05-27 08:23 Harrie Rodenbach <marktenveiling@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Harrie Rodenbach @ 2010-05-27 08:23 UTC (permalink / raw)
To: pgsql-sql
Hi,
We are trying to make use of module ODBC-link. We follow the
instructions as read in README.TXT, including the given examples.
Connecting to an external Oracle database is successful: = oratest=#
select odbclink.connect('ONT_KIS', 'sbm_beheer', 'password');
= connect
= ---------
= 1
= (1 row)
Then we try to select some data:
= oratest=# select odbclink.query(1, 'SELECT * FROM mytable') as
result(id int4, t text, d decimal); = ERROR: syntax error at or near
"(" = LINE 1: ...bclink.query(1, 'SELECT * FROM mytable') as
result(id int4, ...
= ^
For some reason "as result(" generates the error. ODBC-link README.TXT
reads: = All three forms of the function odbclink.query() returns
"SETOF RECORD", so = 1. it can return different query results = 2.
it must be properly casted to the expected result structure
The ODBC connection seems to work, when I select without using the "as
result"-part, I get another error message. = oratest=# select
odbclink.query(1, 'SELECT * FROM mytable'); = ERROR: function
returning record called in context that cannot accept type record
So it seems the functions returns data, but I do not know how to
display it. Since I do not understand how to handle SETOF RECORD I can
not solve the problem myself.
We're running PostgreSQL 8.4.3, ODBC-link 1.0.
Any help will be highly appreciated.
TIA,
Harrie Rodenbach
--
--
Met vriendelijke groet / with kind regards,
Harrie Rodenbach
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Do not understand "SETOF RECORD" - therefore can not use ODBC-link
@ 2010-05-27 14:05 Tom Lane <tgl@sss.pgh.pa.us>
parent: Harrie Rodenbach <marktenveiling@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2010-05-27 14:05 UTC (permalink / raw)
To: Harrie Rodenbach <marktenveiling@gmail.com>; +Cc: pgsql-sql
Harrie Rodenbach <marktenveiling@gmail.com> writes:
> = oratest=# select odbclink.query(1, 'SELECT * FROM mytable') as
> result(id int4, t text, d decimal); = ERROR: syntax error at or near
> "(" = LINE 1: ...bclink.query(1, 'SELECT * FROM mytable') as
> result(id int4, ...
You need that to be select * from odbclink.query ...
the "as" business is only allowed in FROM clause.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Do not understand "SETOF RECORD" - therefore can not use ODBC-link
@ 2010-05-28 07:32 Harrie Rodenbach <marktenveiling@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Harrie Rodenbach @ 2010-05-28 07:32 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
Hi,
2010/5/27 Tom Lane <tgl@sss.pgh.pa.us>:
> Harrie Rodenbach <marktenveiling@gmail.com> writes:
>> = oratest=# select odbclink.query(1, 'SELECT * FROM mytable') as
>> result(id int4, t text, d decimal); = ERROR: syntax error at or near
>> "(" = LINE 1: ...bclink.query(1, 'SELECT * FROM mytable') as
>> result(id int4, ...
>
> You need that to be select * from odbclink.query ...
> the "as" business is only allowed in FROM clause.
>
> regards, tom lane
>
That did the trick, thank you Tom. Next problem will be described in a
new thread.
--
Met vriendelijke groet / with kind regards,
Harrie Rodenbach
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2010-05-28 07:32 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-05-27 08:23 Do not understand "SETOF RECORD" - therefore can not use ODBC-link Harrie Rodenbach <marktenveiling@gmail.com>
2010-05-27 14:05 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-05-28 07:32 ` Harrie Rodenbach <marktenveiling@gmail.com>
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