Redirect postgres connectby into a table

Question

I am using postgres 9.6 with the tablefunc expansion to generate a hierarchial table using the connectby function. My syntax is

SELECT * 
FROM connectby('depTree2', 'id', 'parentid', 'sequence', '{4472}',0)
AS t(keyid text, parent_keyid text, level int, pos int)

Once I collect get the data there is more post processing that I would like to do with it so I need to direct the output of the connectby command into a table. I tried adding INTO on either side of the AS phrase but no luck. Is this possible to do?

score 0 · Accepted Answer · answered Jun 08 '20 at 15:54

Do you wat insert ... select?

INSERT INTO target_table(keyid text, parent_keyid text, level int, pos int)
SELECT * 
FROM connectby('depTree2', 'id', 'parentid', 'sequence', '{4472}',0)
AS t(keyid text, parent_keyid text, level int, pos int)

This works if you have an already-existing table. On the other hand if ou want to create a new table, then:

CREATE TABLE target_table AS
SELECT * FROM connectby('depTree2', 'id', 'parentid', 'sequence', '{4472}',0)
AS t(keyid text, parent_keyid text, level int, pos int)

Redirect postgres connectby into a table

1 Answers1