How to Create View in PostgreSQL DB with Output Data PostSQL tool?

[kanemku]

Hello,

I am trying to use the Post Create SQL Statement to create a VIEW in one schema, A, from another schema, B. 

CREATE OR REPLACE VIEW "A"."newVIEW" AS

SELECT * FROM "B"."oldTABLE";

I've tried several different methods, but I continuously get errors saying: ERROR: relation "oldTABLE" already exists. I also get an error saying: relation "oldTABLE" does not exist.

I've tried: changing all references to serial, all primary keys to integers to match up types, dropped tables, and recreated them. I still get this error.

Any help is appreciated, and if more info is needed, I'm glad to provide it. 

[apathetichell]

is B on the same DB as A? can you pull in B.oldTABLE in input data/connect in-db?

[kanemku]

@apathetichell Yes, both schemas A and B are in the same DB, and I'm able to successfully pull from B.oldTABLE in the input data. 

[apathetichell]

hmmm... I guess my next question would be - do you have create view permissions?

https://stackoverflow.com/questions/27403852/postgres-table-privileges-do-not-allow-user-to-create-s...

and just to be clear - if you are using in-db you can set up a  join from a table in A to a table in B without erroring?

[kanemku]

I'm using the postgres superuser account. 

I just tried using the Connect In-DB tool to join two tables from each schema, and it worked successfully with the Join In-DB tool. 

[simonaubert_bd]

Hello,

You can't create natively a view with in database Alteryx. However, I have developed a macro that retrieves your table create code (with a dynamic output tool) and transform it into a view (with formula) and reinject it in database (with pre-sql statement of a classic output tool).

Here something like that : (without the dynamic output and formula thing) . Please notice you will need a dummy table because you can't pass free sql

You can vote for this idea to support view creation in Alteryx :
https://community.alteryx.com/t5/Alteryx-Designer-Ideas/In-DB-Create-View/idi-p/157886#comments

Best regards,

Simon

[simonaubert_bd]

@kanemkuHello. Here the said macro.

[gaia_fnt]

Hi simonaubert_db, can you please explain a bit more in details how this macro works?

Thank you

Gaia

[simonaubert_bd]

Hello @gaia_fnt 

Quickly :
-batch macro with three parameters : in memory connection (aka..), name of the view, select query to create the view

-I use the pre-sql statement of an input box since I can write any sql in it.

Best regards,

Simon