Passing values to db_link_exec query in PostgreSQL

Question

I need to insert some info I'm reading from a database into a table that belongs to a different one.

I have my info in a RECORD variable, let's call it my_row.

Obviously when I try

SELECT dblink_exec('INSERT INTO remote_table VALUES (my_row.*)');

it doesn't recognize who my_row is.

What other options do I have?

Not sure if * is supported in insert, either way it's bad habit. Try insert into (col1,col2) select x,y from my_row; — Kuberchaun
– Kuberchaun, Commented Jan 28, 2013 at 13:39
It is supported in insert, I run an example earlier (same database, without the dblink) and it did work. I totally agree with what you say about it being a bad habit, and I generally don't do it, but in this case and for exemplification reasons (my full table has about 16 columns) I think it's okay. I promise to change it in the final code :) — andreea
– andreea, Commented Jan 28, 2013 at 13:51

Kuberchaun · Accepted Answer · 2013-01-28 15:55:25Z

1

Here's one option:

CREATE TABLE junk(a text, b text,c TIMESTAMP, d hstore);

DO
$BODY$
DECLARE 
RecordVar junk%rowtype;
SqlCommand TEXT:= 'INSERT INTO junk(a,b,c,d) VALUES(';
BEGIN
   RecordVar.a = 'junk''''a';
   RecordVar.b = 'junkb';
   RecordVar.c = NOW();
   RecordVar.d = '"key1"=>"value1"';
   SqlCommand := SqlCommand  || '''' || RecordVar.a || '''' || ',' || '''' || RecordVar.b || '''' || ',' ||'''' || RecordVar.c || '''' || ',' ||'''' || RecordVar.d || '''' || ');';

   RAISE NOTICE 'SQL = %',SqlCommand;

   PERFORM dblink('dblink_logging',SqlCommand);

END;
$BODY$

    Select a,b,c from junk;
    junk'a|junkb|2013-01-28 09:53:22.308|"key1"=>"value1"
    DROP TABLE junk;

edited Jan 28, 2013 at 15:55

answered Jan 28, 2013 at 14:04

Kuberchaun

30.5k8 gold badges54 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

andreea Over a year ago

This would be a great solution if my data didn't include timstamps and strings with plenty of apostrophes :( I am hoping for a solution that would allow me to send other types of objects besides string. Although admittedly I am very not certain there is one.

Kuberchaun Over a year ago

See if this works for you I updated to include text with ' and a column with a timestamp.

andreea Over a year ago

It does work better, thank you. Now it seems I only have problems with the few columns that are type hstore.

Kuberchaun Over a year ago

Updated to include a hstore column does this give you what you need?

andreea Over a year ago

In theory yes, it should solve my problem, but I've already tried doing it this way and in my case it throws a "failed to find conversion function from unknown to text" error.

Ihor Romanchenko · Accepted Answer · 2013-01-28 13:40:26Z

1

Try something like:

SELECT dblink_exec('INSERT INTO remote_table VALUES '|| quote_literal(my_row.*));

The idea - to use dynamic SQL. To convert the row to its text representation, escape special chars and form the INSERT statement.

answered Jan 28, 2013 at 13:40

Ihor Romanchenko

29k9 gold badges56 silver badges45 bronze badges

2 Comments

Kuberchaun Over a year ago

In my opinion not naming the target columns and source columns is lazy and is bad habit.

andreea Over a year ago

Sadly, this doesn't work as it won't properly format the output -- ie won't put apostrophes around the strings. And also I worry about problems that may show up when changing the timestamps to string and back.

Collectives™ on Stack Overflow

Passing values to db_link_exec query in PostgreSQL

2 Answers 2

5 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related