From: Joe Conway <joe.conway@mail.com>
To: pgsql-interfaces@postgresql.org
Subject: remote database queries
Date: Sun, 27 May 2001 22:02:17 -0700
Message-ID: <002901c0e733$5e683590$0705a8c0@jecw2k1> (raw)
References: <Pine.LNX.4.30.0105271914200.14985-100000@commandprompt.com>
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)::text as vl_acct_pass_phrase
,dblink_tok(t1.f1,'~',4)::text as vl_email_relname
,dblink_tok(t1.f1,'~',5)::text as vl_hwsn_relname
,dblink_tok(t1.f1,'~',6)::timestamp as vl_mod_dt
,dblink_tok(t1.f1,'~',7)::int as 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
Attachments:
dblink.h (application/octet-stream, 1.9K) download
dblink.c (application/octet-stream, 8.0K) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-interfaces@postgresql.org
Cc: joe.conway@mail.com
Subject: Re: remote database queries
In-Reply-To: <002901c0e733$5e683590$0705a8c0@jecw2k1>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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