W dniu 2011-08-18 16:26, Jenish Vyas pisze:
Hi All,
Can we user dblink for insert and update of dynamic values??
Sample function :
CREATE OR REPLACE FUNCTION dblink_test()
RETURNS boolean AS
$BODY$
DECLARE
v1 numeric;
v2 character varying(50);
BEGIN
v1 := 123; v2 := 'asdasdasd';
select dblink_exec('dbname=testing_db_link_1'::text, 'insert into abc
values(v1,v2);'::text);
select dblink_exec('dbname=testing_db_link_1'::text, 'update abc set b = '' testing '' where a =
v1;'::text);
RETURN FALSE;
END;
$BODY$
LANGUAGE plpgsql STABLE
COST 100;
When I am running this function it is giving me following error..
ERROR: column "v1" does not exist
SQL state: 42703
Context: Error occurred on dblink connection named "unnamed": could not execute
command.
SQL statement "select dblink_exec('dbname=testing_db_link_1'::text, 'insert into abc
values(v1,v2);'::text)"
PL/pgSQL function "aaa" line 9 at SQL statement
What is really dynamic in your example, is the query argument to the
dblink_exec(connstr, query).
So first construct query := '...' to contain actual literal values you need to insert into remote
table and then use the result in dblink_exec call.
Queries are interpreted exactly as they are written at the remote end, while the v1 and v2 are only
variables defined locally in your plpgsql function. That's why remote does not know anything about
such "columns".
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
