pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedremote database queries
6+ messages / 4 participants
[nested] [flat]
* remote database queries
@ 2001-05-28 05:02 Joe Conway <joe.conway@mail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Joe Conway @ 2001-05-28 05:02 UTC (permalink / raw)
To: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [INTERFACES] remote database queries
@ 2001-05-28 21:11 Tom Lane <tgl@sss.pgh.pa.us>
parent: Joe Conway <joe.conway@mail.com>
0 siblings, 2 replies; 6+ messages in thread
From: Tom Lane @ 2001-05-28 21:11 UTC (permalink / raw)
To: Joe Conway <joe.conway@mail.com>; +Cc: pgsql-hackers@postgreSQL.org
[ Redirected away from the entirely inappropriate pgsql-interfaces list ]
"Joe Conway" <joe.conway@mail.com> writes:
> 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.
> 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?
It could return a tuple, but there are notational problems that would
make it difficult to do anything useful with the tuple; in particular,
AFAICS you couldn't retrieve more than one column out of it, so there's
no point.
Until we fix that (maybe for 7.2, maybe not) your existing hack is
probably pretty reasonable. You could save some cycles by avoiding
conversion to text, though --- instead return an opaque datum that is
pointer-to-tuple-slot and let the dblink_tok function extract fields
from the tuple. Look at SQL function support and the FieldSelect
expression node type for inspiration.
> 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?
I think this would work if the planner weren't so enthusiastic about
trying to collapse the sub-SELECT query together with the main query.
Unfortunately it doesn't check to see if any set-valued functions are
involved before it collapses 'em --- leaving you with a set-valued
function call in the WHERE clause. Not sure if this is worth fixing,
considering that using set-valued functions in this way is just a
band-aid that doesn't have a long life expectancy.
If you just need a hack with a short life expectancy, here's a hack that
I recommend not reading right before dinner ... might make you lose your
appetite ;-). Build the view as a dummy UNION:
create view vs_lkup_rm as
select ... from (select dblink(...))
union all
select null, null, ... where false;
Done this way, the UNION won't change the view's results --- but it will
prevent the (current version of the) planner from collapsing the view
together with the surrounding query. For example:
regression=# create function dblink() returns setof int as
regression-# 'select f1 from int4_tbl' language 'sql';
CREATE
regression=# select dblink();
?column?
-------------
0
123456
-123456
2147483647
-2147483647
(5 rows)
regression=# create view vv1 as
regression-# select f1 from (select dblink() as f1) t1;
CREATE
regression=# select * from vv1 where f1 > 0;
ERROR: Set-valued function called in context that cannot accept a set
regression=# create view vv2 as
regression-# select f1 from (select dblink() as f1) t1
regression-# union all
regression-# select null where false;
CREATE
regression=# select * from vv2;
f1
-------------
0
123456
-123456
2147483647
-2147483647
(5 rows)
regression=# select * from vv2 where f1 > 0;
f1
------------
123456
2147483647
(2 rows)
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Re: [INTERFACES] remote database queries
@ 2001-05-29 02:55 Joe Conway <joe@conway-family.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 6+ messages in thread
From: Joe Conway @ 2001-05-29 02:55 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-hackers@postgresql.org
> [ Redirected away from the entirely inappropriate pgsql-interfaces list ]
oops . . . sorry!
> Until we fix that (maybe for 7.2, maybe not) your existing hack is
> probably pretty reasonable. You could save some cycles by avoiding
> conversion to text, though --- instead return an opaque datum that is
> pointer-to-tuple-slot and let the dblink_tok function extract fields
> from the tuple. Look at SQL function support and the FieldSelect
> expression node type for inspiration.
Thanks -- I'll take a look.
> I think this would work if the planner weren't so enthusiastic about
> trying to collapse the sub-SELECT query together with the main query.
> Unfortunately it doesn't check to see if any set-valued functions are
> involved before it collapses 'em --- leaving you with a set-valued
> function call in the WHERE clause. Not sure if this is worth fixing,
> considering that using set-valued functions in this way is just a
> band-aid that doesn't have a long life expectancy.
I'd certainly bow to your wisdom in this area, but I was thinking it would
be useful (at times) to force a FROM clause sub-select to be treated as if
it were a "real" table (probably not the best way to express this, but
hopefully you get the idea). In MSSQL I've found many situations where
putting intermediate results into a temp table, and then joining to it, is
significantly faster than letting the optimizer do it's best. But the fact
that MSSQL will return record sets from a stored procedure help makes this
tolerable/manageable -- i.e. the whole ugly mess can be rolled into one nice
neat strored procedure call. If the FROM clause sub-select could be treated,
in a sense, like an on-the-fly temp table in PostgreSQL, a similar result is
possible. And if the whole ugly mess is rolled up behind a view, no one has
to know except the especially curious ;-)
>
> If you just need a hack with a short life expectancy, here's a hack that
> I recommend not reading right before dinner ... might make you lose your
> appetite ;-). Build the view as a dummy UNION:
*Very* few things make me lose my appetite -- and this worked perfectly!
Thank you.
-- Joe
^ permalink raw reply [nested|flat] 6+ messages in thread
* remote database queries
@ 2001-05-31 04:59 Joe Conway <joe.conway@mail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 2 replies; 6+ messages in thread
From: Joe Conway @ 2001-05-31 04:59 UTC (permalink / raw)
To: pgsql-hackers@postgresql.org
> Until we fix that (maybe for 7.2, maybe not) your existing hack is
> probably pretty reasonable. You could save some cycles by avoiding
> conversion to text, though --- instead return an opaque datum that is
> pointer-to-tuple-slot and let the dblink_tok function extract fields
> from the tuple. Look at SQL function support and the FieldSelect
> expression node type for inspiration.
>
I changed the dblink() function to return a pointer instead of concatenated
text, and dblink_tok() to use the pointer. FWIW, a query on a small (85
tuples) remote (a second PC on a 100baseT subnet) table takes about 34
milliseconds (based on show_query_stats) versus about 4 milliseconds when
run locally. It actually takes a bit longer (~65 milliseconds) when run
against a second database on the same PC. The original text parsing version
was about 25% slower.
Although shifting from text parsing to pointer passing is more efficient, I
have one more question regarding this -- for now ;) -- is there any way to
check the pointer passed to dblink_tok() to be sure it came from dblink()?
Thanks,
-- Joe
Attachments:
dblink.patch (application/octet-stream, 15.6K) download
diff -Naur pgsql.virg/contrib/dblink/Makefile pgsql/contrib/dblink/Makefile
--- pgsql.virg/contrib/dblink/Makefile Thu Jan 1 00:00:00 1970
+++ pgsql/contrib/dblink/Makefile Thu May 31 04:15:26 2001
@@ -0,0 +1,48 @@
+subdir = contrib/dblink
+top_builddir = ../..
+include $(top_builddir)/src/Makefile.global
+include $(top_builddir)/src/Makefile.shlib
+include_srcdir = $(top_builddir)/src/include
+
+
+NAME := dblink
+SONAME := $(NAME)$(DLSUFFIX)
+
+override CFLAGS += -I$(srcdir)
+override CFLAGS += -I$(include_srcdir)
+override CFLAGS += -I$(libpq_srcdir)
+override CFLAGS += $(CFLAGS_SL)
+
+OBJS = $(NAME).o
+
+all: $(OBJS) $(SONAME) $(NAME).sql
+
+$(OBJS): $(NAME).c
+ $(CC) -o $@ -c $(CFLAGS) $<
+
+$(SONAME): $(OBJS)
+ $(LD) -o $@ -Bshareable $(libpq) $<
+
+$(NAME).sql: $(NAME).sql.in
+ sed -e 's:MODULE_PATHNAME:$(libdir)/contrib/$(SONAME):g' < $< > $@
+
+install: all installdirs
+ $(INSTALL_DATA) README.$(NAME) $(docdir)/contrib
+ $(INSTALL_DATA) $(NAME).sql $(datadir)/contrib
+ $(INSTALL_SHLIB) $(SONAME) $(libdir)/contrib
+
+installdirs:
+ $(mkinstalldirs) $(docdir)/contrib $(datadir)/contrib $(libdir)/contrib
+
+uninstall:
+ rm -rf $(docdir)/contrib/README.$(NAME) $(datadir)/contrib/$(NAME).sql $(libdir)/contrib/$(SONAME)
+
+clean distclean maintainer-clean:
+ rm -f $(SONAME) *.o *.sql
+
+depend dep:
+ $(CC) -MM -MG $(CFLAGS) *.c > depend
+
+ifeq (depend,$(wildcard depend))
+include depend
+endif
diff -Naur pgsql.virg/contrib/dblink/README.dblink pgsql/contrib/dblink/README.dblink
--- pgsql.virg/contrib/dblink/README.dblink Thu Jan 1 00:00:00 1970
+++ pgsql/contrib/dblink/README.dblink Wed May 30 06:03:21 2001
@@ -0,0 +1,157 @@
+/*
+ * dblink
+ *
+ * Functions returning results from a remote database
+ *
+ * Copyright (c) Joseph Conway <joe.conway@mail.com>, 2001;
+ *
+ * Permission to use, copy, modify, and distribute this software and its
+ * documentation for any purpose, without fee, and without a written agreement
+ * is hereby granted, provided that the above copyright notice and this
+ * paragraph and the following two paragraphs appear in all copies.
+ *
+ * IN NO EVENT SHALL THE AUTHOR OR DISTRIBUTORS BE LIABLE TO ANY PARTY FOR
+ * DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING
+ * LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS
+ * DOCUMENTATION, EVEN IF THE AUTHOR OR DISTRIBUTORS HAVE BEEN ADVISED OF THE
+ * POSSIBILITY OF SUCH DAMAGE.
+ *
+ * THE AUTHOR AND DISTRIBUTORS SPECIFICALLY DISCLAIMS ANY WARRANTIES,
+ * INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY
+ * AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS
+ * ON AN "AS IS" BASIS, AND THE AUTHOR AND DISTRIBUTORS HAS NO OBLIGATIONS TO
+ * PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.
+ *
+ */
+
+Version 0.2 (29 May, 2001):
+ Function to test returning data set from remote database
+ Tested under Linux (Red Hat 6.2 and 7.0) and PostgreSQL 7.1 and 7.2devel
+
+Release Notes:
+
+ Version 0.2
+ - initial release
+
+Installation:
+ Place these files in a directory called 'dblink' under 'contrib' in the PostgreSQL source tree. Then run:
+
+ make
+ make install
+
+ You can use dblink.sql to create the functions in your database of choice, e.g.
+
+ psql -U postgres template1 < dblink.sql
+
+ installs following functions into database template1:
+
+ dblink() - returns a pointer to results from remote query
+ dblink_tok() - extracts and returns individual field results
+
+Documentation
+==================================================================
+Name
+
+dblink -- Returns a pointer to a data set from a remote database
+
+Synopsis
+
+dblink(text connstr, text sql)
+
+Inputs
+
+ connstr
+
+ standard libpq format connection srting,
+ e.g. "hostaddr=127.0.0.1 port=5432 dbname=mydb user=postgres password=mypasswd"
+
+ sql
+
+ sql statement that you wish to execute on the remote host
+ e.g. "select * from pg_class"
+
+Outputs
+
+ Returns setof int (pointer)
+
+Example usage
+
+ select dblink('hostaddr=127.0.0.1 port=5432 dbname=mydb user=postgres password=mypasswd','select f1, f2 from mytable');
+
+
+==================================================================
+
+Name
+
+dblink_tok -- Returns individual select field results from a dblink remote query
+
+Synopsis
+
+dblink_tok(int pointer, int fnumber)
+
+Inputs
+
+ pointer
+
+ a pointer returned by a call to dblink()
+
+ fnumber
+
+ the ordinal position (zero based) of the field to be returned from the dblink result set
+
+Outputs
+
+ Returns text
+
+Example usage
+
+ select dblink_tok(t1.dblink_p,0) as f1, dblink_tok(t1.dblink_p,1) as f2
+ from (select dblink('hostaddr=127.0.0.1 port=5432 dbname=mydb user=postgres password=mypasswd'
+ ,'select f1, f2 from mytable') as dblink_p) as t1;
+
+
+==================================================================
+
+NOTE: If you need to provide selection criteria in a WHERE clause, it is necessary
+to 'fake' a UNION, e.g.
+
+ select
+ dblink_tok(t1.dblink_p,0) as f1
+ ,dblink_tok(t1.dblink_p,1) as f2
+ from
+ (
+ select dblink(
+ 'hostaddr=127.0.0.1 port=5432 dbname=mydb user=postgres password=mypasswd'
+ ,'select f1, f2 from mytable'
+ ) as dblink_p
+ union
+ select null,null where false
+ ) as t1
+ where
+ f1 = 'mycriteria';
+
+in order to work around an issue with the query optimizer. A more convenient way to approach
+this problem is to create a view:
+
+ create view myremotetable as
+ select
+ dblink_tok(t1.dblink_p,0) as f1
+ ,dblink_tok(t1.dblink_p,1) as f2
+ from
+ (
+ select dblink(
+ 'hostaddr=127.0.0.1 port=5432 dbname=mydb user=postgres password=mypasswd'
+ ,'select f1, f2 from mytable'
+ ) as dblink_p
+ union
+ select null,null where false
+ ) as t1;
+
+Then you can simply write:
+
+ select f1,f2 from myremotetable where f1 = 'mycriteria';
+
+==================================================================
+
+-- Joe Conway
+
diff -Naur pgsql.virg/contrib/dblink/dblink.c pgsql/contrib/dblink/dblink.c
--- pgsql.virg/contrib/dblink/dblink.c Thu Jan 1 00:00:00 1970
+++ pgsql/contrib/dblink/dblink.c Wed May 30 05:13:23 2001
@@ -0,0 +1,276 @@
+/*
+ * dblink.c
+ *
+ * Functions returning results from a remote database
+ *
+ * Copyright (c) Joseph Conway <joe.conway@mail.com>, 2001;
+ *
+ * Permission to use, copy, modify, and distribute this software and its
+ * documentation for any purpose, without fee, and without a written agreement
+ * is hereby granted, provided that the above copyright notice and this
+ * paragraph and the following two paragraphs appear in all copies.
+ *
+ * IN NO EVENT SHALL THE AUTHOR OR DISTRIBUTORS BE LIABLE TO ANY PARTY FOR
+ * DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING
+ * LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS
+ * DOCUMENTATION, EVEN IF THE AUTHOR OR DISTRIBUTORS HAVE BEEN ADVISED OF THE
+ * POSSIBILITY OF SUCH DAMAGE.
+ *
+ * THE AUTHOR AND DISTRIBUTORS SPECIFICALLY DISCLAIMS ANY WARRANTIES,
+ * INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY
+ * AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS
+ * ON AN "AS IS" BASIS, AND THE AUTHOR AND DISTRIBUTORS HAS NO OBLIGATIONS TO
+ * PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.
+ *
+ */
+
+#include "dblink.h"
+
+PG_FUNCTION_INFO_V1(dblink);
+Datum
+dblink(PG_FUNCTION_ARGS)
+{
+ PGconn *conn = NULL;
+ PGresult *res = NULL;
+ dblink_results *results;
+ char *optstr;
+ char *sqlstatement;
+ char *curstr = "DECLARE mycursor CURSOR FOR ";
+ char *execstatement;
+ char *msg;
+ int ntuples = 0;
+ ReturnSetInfo *rsi;
+
+ if (PG_ARGISNULL(0) || PG_ARGISNULL(1)) {
+ elog(ERROR, "dblink: NULL arguments are not permitted");
+ }
+
+ if (fcinfo->resultinfo == NULL || ! IsA(fcinfo->resultinfo, ReturnSetInfo)) {
+ elog(ERROR, "dblink: function called in context that does not accept a set result");
+ }
+
+ optstr = DatumGetCString(DirectFunctionCall1(textout, PointerGetDatum(PG_GETARG_TEXT_P(0))));
+ sqlstatement = DatumGetCString(DirectFunctionCall1(textout, PointerGetDatum(PG_GETARG_TEXT_P(1))));
+
+ if (fcinfo->flinfo->fn_extra == NULL) {
+
+ conn = PQconnectdb(optstr);
+ if (PQstatus(conn) == CONNECTION_BAD)
+ {
+ msg = PQerrorMessage(conn);
+ PQfinish(conn);
+ elog(ERROR, "dblink: connection error: %s", msg);
+ }
+
+ res = PQexec(conn, "BEGIN");
+ if (PQresultStatus(res) != PGRES_COMMAND_OK)
+ {
+ msg = PQerrorMessage(conn);
+ PQclear(res);
+ PQfinish(conn);
+ elog(ERROR, "dblink: begin error: %s", msg);
+ }
+ PQclear(res);
+
+ execstatement = (char *) palloc(strlen(curstr) + strlen(sqlstatement) + 1);
+ if (execstatement != NULL) {
+ strcpy(execstatement, curstr);
+ strcat(execstatement, sqlstatement);
+ strcat(execstatement, "\0");
+ } else {
+ elog(ERROR, "dblink: insufficient memory" );
+ }
+
+ res = PQexec(conn, execstatement);
+ if (!res || (PQresultStatus(res) != PGRES_COMMAND_OK && PQresultStatus(res) != PGRES_TUPLES_OK))
+ {
+ msg = PQerrorMessage(conn);
+ PQclear(res);
+ PQfinish(conn);
+ elog(ERROR, "dblink: sql error: %s", msg);
+ } else {
+ /*
+ * got results, start fetching them
+ */
+ PQclear(res);
+
+ res = PQexec(conn, "FETCH ALL in mycursor");
+ if (!res || PQresultStatus(res) != PGRES_TUPLES_OK) {
+ msg = PQerrorMessage(conn);
+ PQclear(res);
+ PQfinish(conn);
+ elog(ERROR, "dblink: sql error: %s", msg);
+ }
+
+ ntuples = PQntuples(res);
+
+ if (ntuples > 0) {
+
+ results = init_dblink_results(fcinfo->flinfo->fn_mcxt);
+ results->tup_num = 0;
+ results->res = res;
+ res = NULL;
+
+ (dblink_results *) fcinfo->flinfo->fn_extra = results;
+
+ results = NULL;
+ results = fcinfo->flinfo->fn_extra;
+
+ /* close the cursor */
+ res = PQexec(conn, "CLOSE mycursor");
+ PQclear(res);
+
+ /* commit the transaction */
+ res = PQexec(conn, "COMMIT");
+ PQclear(res);
+
+ /* close the connection to the database and cleanup */
+ PQfinish(conn);
+
+ rsi = (ReturnSetInfo *)fcinfo->resultinfo;
+ rsi->isDone = ExprMultipleResult;
+
+ PG_RETURN_POINTER(results);
+
+ } else {
+
+ PQclear(res);
+
+ /* close the cursor */
+ res = PQexec(conn, "CLOSE mycursor");
+ PQclear(res);
+
+ /* commit the transaction */
+ res = PQexec(conn, "COMMIT");
+ PQclear(res);
+
+ /* close the connection to the database and cleanup */
+ PQfinish(conn);
+
+ rsi = (ReturnSetInfo *)fcinfo->resultinfo;
+ rsi->isDone = ExprEndResult ;
+
+ PG_RETURN_NULL();
+ }
+ }
+ } else {
+ /*
+ * check for more results
+ */
+
+ results = fcinfo->flinfo->fn_extra;
+ results->tup_num++;
+ ntuples = PQntuples(results->res);
+
+ if (results->tup_num < ntuples) {
+
+ /*
+ * fetch them if available
+ */
+
+ rsi = (ReturnSetInfo *)fcinfo->resultinfo;
+ rsi->isDone = ExprMultipleResult;
+
+ PG_RETURN_POINTER(results);
+
+ } else {
+
+ /*
+ * or if no more, clean things up
+ */
+
+ results = fcinfo->flinfo->fn_extra;
+
+ PQclear(results->res);
+
+ rsi = (ReturnSetInfo *)fcinfo->resultinfo;
+ rsi->isDone = ExprEndResult ;
+
+ PG_RETURN_NULL();
+ }
+ }
+ PG_RETURN_NULL();
+}
+
+
+/*
+ * dblink_tok
+ * parse dblink output string
+ * return fldnum item (0 based)
+ * based on provided field separator
+ */
+
+PG_FUNCTION_INFO_V1(dblink_tok);
+Datum
+dblink_tok(PG_FUNCTION_ARGS)
+{
+ dblink_results *results;
+ int fldnum;
+ text *result_text;
+ char *result;
+ int nfields = 0;
+ int text_len = 0;
+
+ if (PG_ARGISNULL(0) || PG_ARGISNULL(1)) {
+ elog(ERROR, "dblink: NULL arguments are not permitted");
+ }
+
+ results = (dblink_results *) PG_GETARG_POINTER(0);
+ if (results == NULL) {
+ elog(ERROR, "dblink: function called with invalid result pointer");
+ }
+
+ fldnum = PG_GETARG_INT32(1);
+ if (fldnum < 0) {
+ elog(ERROR, "dblink: field number < 0 not permitted");
+ }
+
+ nfields = PQnfields(results->res);
+ if (fldnum > (nfields - 1)) {
+ elog(ERROR, "dblink: field number %d does not exist", fldnum);
+ }
+
+ text_len = PQgetlength(results->res, results->tup_num, fldnum);
+
+ result = (char *) palloc(text_len + 1);
+
+ if (result != NULL) {
+ strcpy(result, PQgetvalue(results->res, results->tup_num, fldnum));
+ strcat(result, "\0");
+ } else {
+ elog(ERROR, "dblink: insufficient memory" );
+ }
+
+ result_text = DatumGetTextP(DirectFunctionCall1(textin, CStringGetDatum(result)));
+
+ PG_RETURN_TEXT_P(result_text);
+}
+
+
+/*
+ * internal functions
+ */
+
+
+/*
+ * init_dblink_results
+ * - create an empty dblink_results data structure
+ */
+dblink_results *
+init_dblink_results(MemoryContext fn_mcxt)
+{
+ MemoryContext oldcontext;
+ dblink_results *retval;
+
+ oldcontext = MemoryContextSwitchTo(fn_mcxt);
+
+ retval = (dblink_results *) palloc(sizeof(dblink_results));
+ MemSet(retval, 0, sizeof(dblink_results));
+
+ retval->tup_num = -1;
+ retval->res = NULL;
+
+ MemoryContextSwitchTo(oldcontext);
+
+ return retval;
+}
diff -Naur pgsql.virg/contrib/dblink/dblink.h pgsql/contrib/dblink/dblink.h
--- pgsql.virg/contrib/dblink/dblink.h Thu Jan 1 00:00:00 1970
+++ pgsql/contrib/dblink/dblink.h Thu May 31 04:15:28 2001
@@ -0,0 +1,70 @@
+/*
+ * dblink.h
+ *
+ * Functions returning results from a remote database
+ *
+ * Copyright (c) Joseph Conway <joe.conway@mail.com>, 2001;
+ *
+ * Permission to use, copy, modify, and distribute this software and its
+ * documentation for any purpose, without fee, and without a written agreement
+ * is hereby granted, provided that the above copyright notice and this
+ * paragraph and the following two paragraphs appear in all copies.
+ *
+ * IN NO EVENT SHALL THE AUTHOR OR DISTRIBUTORS BE LIABLE TO ANY PARTY FOR
+ * DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING
+ * LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS
+ * DOCUMENTATION, EVEN IF THE AUTHOR OR DISTRIBUTORS HAVE BEEN ADVISED OF THE
+ * POSSIBILITY OF SUCH DAMAGE.
+ *
+ * THE AUTHOR AND DISTRIBUTORS SPECIFICALLY DISCLAIMS ANY WARRANTIES,
+ * INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY
+ * AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS
+ * ON AN "AS IS" BASIS, AND THE AUTHOR AND DISTRIBUTORS HAS NO OBLIGATIONS TO
+ * PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.
+ *
+ */
+
+#ifndef DBLINK_H
+#define DBLINK_H
+
+#include <string.h>
+#include "postgres.h"
+#include "libpq-fe.h"
+#include "libpq-int.h"
+#include "fmgr.h"
+#include "access/tupdesc.h"
+#include "executor/executor.h"
+#include "nodes/nodes.h"
+#include "nodes/execnodes.h"
+#include "utils/builtins.h"
+
+/*
+ * This struct holds the results of the remote query.
+ * Use fn_extra to hold a pointer to it across calls
+ */
+typedef struct
+{
+ /*
+ * last tuple number accessed
+ */
+ int tup_num;
+
+ /*
+ * the actual query results
+ */
+ PGresult *res;
+
+} dblink_results;
+
+/*
+ * External declarations
+ */
+extern Datum dblink(PG_FUNCTION_ARGS);
+extern Datum dblink_tok(PG_FUNCTION_ARGS);
+
+/*
+ * Internal declarations
+ */
+dblink_results *init_dblink_results(MemoryContext fn_mcxt);
+
+#endif /* DBLINK_H */
diff -Naur pgsql.virg/contrib/dblink/dblink.sql.in pgsql/contrib/dblink/dblink.sql.in
--- pgsql.virg/contrib/dblink/dblink.sql.in Thu Jan 1 00:00:00 1970
+++ pgsql/contrib/dblink/dblink.sql.in Wed May 30 04:49:32 2001
@@ -0,0 +1,5 @@
+CREATE FUNCTION dblink (text,text) RETURNS setof int
+ AS 'MODULE_PATHNAME','dblink' LANGUAGE 'c';
+
+CREATE FUNCTION dblink_tok (int,int) RETURNS text
+ AS 'MODULE_PATHNAME','dblink_tok' LANGUAGE 'c';
* Re: remote database queries
@ 2001-06-12 18:09 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Joe Conway <joe.conway@mail.com>
1 sibling, 0 replies; 6+ messages in thread
From: Bruce Momjian @ 2001-06-12 18:09 UTC (permalink / raw)
To: Joe Conway <joe.conway@mail.com>; +Cc: pgsql-hackers@postgresql.org
Your patch has been added to the PostgreSQL unapplied patches list at:
http://candle.pha.pa.us/cgi-bin/pgpatches
I will try to apply it within the next 48 hours.
> > Until we fix that (maybe for 7.2, maybe not) your existing hack is
> > probably pretty reasonable. You could save some cycles by avoiding
> > conversion to text, though --- instead return an opaque datum that is
> > pointer-to-tuple-slot and let the dblink_tok function extract fields
> > from the tuple. Look at SQL function support and the FieldSelect
> > expression node type for inspiration.
> >
>
> I changed the dblink() function to return a pointer instead of concatenated
> text, and dblink_tok() to use the pointer. FWIW, a query on a small (85
> tuples) remote (a second PC on a 100baseT subnet) table takes about 34
> milliseconds (based on show_query_stats) versus about 4 milliseconds when
> run locally. It actually takes a bit longer (~65 milliseconds) when run
> against a second database on the same PC. The original text parsing version
> was about 25% slower.
>
> Although shifting from text parsing to pointer passing is more efficient, I
> have one more question regarding this -- for now ;) -- is there any way to
> check the pointer passed to dblink_tok() to be sure it came from dblink()?
>
> Thanks,
>
> -- Joe
>
[ Attachment, skipping... ]
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: remote database queries
@ 2001-06-14 16:48 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Joe Conway <joe.conway@mail.com>
1 sibling, 0 replies; 6+ messages in thread
From: Bruce Momjian @ 2001-06-14 16:48 UTC (permalink / raw)
To: Joe Conway <joe.conway@mail.com>; +Cc: pgsql-hackers@postgresql.org
Added to /contrib. Thanks.
> > Until we fix that (maybe for 7.2, maybe not) your existing hack is
> > probably pretty reasonable. You could save some cycles by avoiding
> > conversion to text, though --- instead return an opaque datum that is
> > pointer-to-tuple-slot and let the dblink_tok function extract fields
> > from the tuple. Look at SQL function support and the FieldSelect
> > expression node type for inspiration.
> >
>
> I changed the dblink() function to return a pointer instead of concatenated
> text, and dblink_tok() to use the pointer. FWIW, a query on a small (85
> tuples) remote (a second PC on a 100baseT subnet) table takes about 34
> milliseconds (based on show_query_stats) versus about 4 milliseconds when
> run locally. It actually takes a bit longer (~65 milliseconds) when run
> against a second database on the same PC. The original text parsing version
> was about 25% slower.
>
> Although shifting from text parsing to pointer passing is more efficient, I
> have one more question regarding this -- for now ;) -- is there any way to
> check the pointer passed to dblink_tok() to be sure it came from dblink()?
>
> Thanks,
>
> -- Joe
>
[ Attachment, skipping... ]
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2001-06-14 16:48 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-05-28 05:02 remote database queries Joe Conway <joe.conway@mail.com>
2001-05-28 21:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-05-29 02:55 ` Joe Conway <joe@conway-family.com>
2001-05-31 04:59 ` Joe Conway <joe.conway@mail.com>
2001-06-12 18:09 ` Bruce Momjian <pgman@candle.pha.pa.us>
2001-06-14 16:48 ` Bruce Momjian <pgman@candle.pha.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