Quantcast

Can we use dblink for insert and update of dynamic values

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Can we use dblink for insert and update of dynamic values

Jenish Vyas
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

Plz. guide me.

Thanks & regards,
JENISH VYAS


Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Ireneusz Pluta


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 ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Loading...