Postgresql dblink

Question

Trying to be lazy when looking at an example

SELECT realestate.address, realestate.parcel, s.sale_year, s.sale_amount, 
        FROM realestate INNER JOIN 
                dblink('dbname=somedb port=5432 host=someserver 
                user=someuser password=somepwd',
                'SELECT parcel_id, sale_year, 
                    sale_amount FROM parcel_sales')
            AS s(parcel_id char(10),sale_year int, sale_amount int)

Is there a way of getting the AS section filled in from the table?

I'm copying data from tables of the same name and structure on different servers. If I can get the structure to copy from the existing table, it will save me a lot of time

Thanks Bruce

Alexis · Accepted Answer · 2012-05-15 07:46:31Z

3

The answer is: No. See the doc:

Since dblink can be used with any query, it is declared to return record, rather than specifying any particular set of columns. This means that you must specify the expected set of columns in the calling query — otherwise PostgreSQL would not know what to expect.

http://www.postgresql.org/docs/9.1/static/contrib-dblink-function.html

Edit: by the way, for a table or a view, you can get the fields name and type in a first query:

select column_name 
from information_schema.columns
where table_name = 'your_table_or_view';

You could then use it to fill the fields declaration.

Alexis

edited May 15, 2012 at 7:46

answered May 15, 2012 at 7:01

Alexis

7159 silver badges35 bronze badges

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

2 Comments

Thorne Over a year ago

Yes but can you do it as AS (select column_name from information_schema.columns where table_name = 'your_table_or_view')

Alexis Over a year ago

No, you cannot do it so directly. But all you have to do is write a simple PLPGSQL function to build your query string and execute it.

Collectives™ on Stack Overflow

Postgresql dblink

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related