Thread: Do not understand "SETOF RECORD" - therefore can not use ODBC-link

Do not understand "SETOF RECORD" - therefore can not use ODBC-link

From
Harrie Rodenbach
Date:
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


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


Re: Do not understand "SETOF RECORD" - therefore can not use ODBC-link

From
Harrie Rodenbach
Date:
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