row => text => row

Marc Mamin <M.Mamin@intershop.de> writes:

It is possible to cast a row to text, but is there a way to revert that?

Well, you can surely cast it back to the rowtype, but I think that answer
doesn't really help you. What you seem to need is not casting to a
rowtype, but "bursting" the rowtype variable into individual columns.

create temp table test like pg_class;
WHITH dummy as (SELECT (c.*)::text t from pg_class c limit 10)
INSERT INTO test
SELECT ???
FROM dummy;

The trick here is to use the rowtype result as a single variable,
and burst it later:

regression=# create temp table test (like pg_class);
CREATE TABLE
regression=# with dummy as (select c from pg_class c limit 10)
regression-# insert into test select (c).* from dummy;
INSERT 0 10

BTW, I assume there's a reason for not simply doing

insert into test select * from pg_class c limit 10;

or even

with dummy as (select * from pg_class c limit 10)
insert into test select * from dummy;

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 11/11/2016 12:55 AM, Marc Mamin wrote:

Hello,

It is possible to cast a row to text, but is there a way to revert that?

e.g.:

create temp table test like pg_class;

WHITH dummy as (SELECT (c.*)::text t from pg_class c limit 10)
INSERT INTO test
SELECT ???
FROM dummy;

an other option that would sometimes help me would be to cast rows as
text,but in their (default) csv representation:

select to_csv((c.*)) from foo c;

In psql(https://www.postgresql.org/docs/9.5/static/app-psql.html):

Long way

\pset fieldsep ','
\pset format unaligned
\pset footer
\o test.csv
SELECT (c.*) t from pg_class c limit 10;
\o

Shorter way:

\copy { table [ ( column_list ) ] | ( query ) } { from | to } {
'filename' | program 'command' | stdin | stdout | pstdin | pstdout } [ [
with ] ( option [, ...] ) ]

Outside psql:

https://www.postgresql.org/docs/9.5/static/sql-copy.html

regards,

Marc Mamin

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general