sqlpostgresqlsql-insertcreate-tableconnect-by

Redirect postgres connectby into a table

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?

Solution

  • 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)