Hello all,
Inspired by others who have recently gotten PostgreSQL functions to return
sets, I set out to create my own. I have on more than one occasion wished I
could run a query across databases or servers (similar to a dblink in Oracle
or a linked db in MSSQL). Attached is a C file which is my attempt. It
exports two functions:
dblink(text connect_string, text sql, text field_separator)
dblink_tok(text delimited_text, text field_separator, int ord_position)
The functions are used as shown in the following example:
select dblink_tok(t1.f1,'~',0)::int as vl_id,dblink_tok(t1.f1,'~',1)::text as vl_guid,dblink_tok(t1.f1,'~',2)::text as
vl_pri_email,dblink_tok(t1.f1,'~',3)::textas vl_acct_pass_phrase,dblink_tok(t1.f1,'~',4)::text as
vl_email_relname,dblink_tok(t1.f1,'~',5)::textas vl_hwsn_relname,dblink_tok(t1.f1,'~',6)::timestamp as
vl_mod_dt,dblink_tok(t1.f1,'~',7)::intas vl_status
from(select dblink('host=192.168.5.150 port=5432 dbname=vsreg_001 user=postgres
password=postgres','select * from vs_lkup','~') as f1) as t1
By doing "create view vs_lkup_rm as . . ." with the above query, from a
database on another server, I can then write:
"select * from vs_lkup" and get results just as if I were on 192.168.5.150
(sort of -- see problem below).
I have one question, and one problem regarding this.
First the question: is there any way to get the dblink function to return
setof composite -- i.e. return tuples instead of scalar values? The
documentation indicates that a function can return a composite type, but my
attempts all seemed to produce only pointer values (to the tuples?)
Now the problem: as I stated above, "select * from vs_lkup" returns results
just as if I were on 192.168.5.150 -- but if I try "select * from vs_lkup
WHERE vl_id = 1" or "select * from vs_lkup WHERE vl_pri_email in
('email1@foo.com')" I get the following error message: "ERROR: Set-valued
function called in context that cannot accept a set". Any ideas how to work
around this?
Thanks,
Joe Conway