PostgreSQL - Execute Snap

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.

That’s strange, can you please try a simple SELECT query and check if you’re getting any data in the output?

yes I get data out of a select snap… @Snaplogic…does the snap I’m asking about not support CTEs?

Try a simple SELECT query on your EXECUTE snap, you should still get your data.

that’s not the point…I want to have 1 or more CTEs building different sets of records and then select from that. this is basic query functionality. I want @Snaplogic to either ack they don’t support, show us how it’s supported, or see it on a roadmap.

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

WITH tbl AS (
    SELECT ID, Name from Names

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

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

@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.

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)

1 Like

@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.

1 Like