Thread: Can we use dblink for insert and update of dynamic values

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".