PostgreSQL - Execute Snap

[chmurray88]

Does the PostgreSQL - Execute Snap not support using CTE’s as part of the query? When I try to include 1 or more CTE’s. all I get out of the snap is a status and message field returned, not the actual data.

[ddellsperger]

It is supported with the PostgreSQL - Execute Snap, I was able to get it working with:

WITH tbl AS (
    SELECT ID, Name from Names
)
SELECT DISTINCT Name from tbl

For example, we only support it with a single statement, but you could attach additional CTEs with comma separating them, though.

[mdawlekar]

Hi @chmurray88 To reply your question , yes Postgres Execute snap supports CTEs as part of a query. I took 2 CTEs and did a sample left join (both CTE is referring to same table with diff filter criteria) and the output result is as expected

My SQL statement in Execute snap
with cte as (

select * from

“all_data_types” where c_int <= 1000 limit 100

)

,

cte2 as

(select * from

“all_data_types” where c_int > 1000 limit 100
)
select * from cte
left join
cte2 on cte.c_smallint=cte2.c_smallint

[chmurray88]

@mdawlekar - thanks for sharing. In my example…I start with one cte…call that cte is the next cte…then select from the 2nd cte. As output all I get is a status and message field, not the fields from the “last” select query.

[ddellsperger]

I just have a placeholder data example, but I see this working in my case:

the SQL Statement is:

with tbl as (
    select id, name from public.names
), tbl2 as (
     select distinct name from tbl
) select name from tbl2 order by name desc;

Do you have a link to your pipeline (or org, project, pipeline name) we can take a look at it (feel free to DM me if you prefer)

[mdawlekar]

@chmurray88 I have used nested CTEs and I am still able to get the output results fields

with inner_cte as (
select * from
“all_data_types” where c_int <= 1000 limit 100)

,
outer_cte as
(select * from
inner_cte where c_int is not null limit 100
)
select * from outer_cte

Please share us your Execute statement that you are using , So I can check and let you know.