Pass multiple postgres SQL statements in a single PGexec call

Question

In t-sql, it's possible to run multiple select statements without a ;. Example: select 1 select 2 is valid, and returns two datasets of 1 and 2 respectively.

In postgres, it is not possible to run multiple select statements... you need a ; delimiter otherwise you get a syntax error.

Referencing the docs: http://www.postgresql.org/docs/current/interactive/libpq-exec.html

Multiple queries sent in a single PQexec call are processed in a single transaction, unless there are explicit BEGIN/COMMIT commands included in the query string to divide it into multiple transactions.

How can I do this?

Let's say I want to run these two queries on the server: select 1 select 2: should it look like this:

begin select 1 commit; begin select 2 commit

I'm ok with it only returning the last query as the result set, but I need to know that the first query was executed on the server, even if it's not returning with that result set.

Why I want to do this: I have a complex sql script that has ~6 temp tables to build that the main query will use. By delimiting the temp tables with the ; syntax, I can't schedule this script in cron to run on a schedule. If I can get the temp tables to run and the main query to access them in the same PGexec call, I'd be very very happy.

From the fine manual http://www.postgresql.org/docs/9.5/static/sql-createtable.html `Temporary tables are automatically dropped at the end of a session, or optionally at the end of the current transaction (see ON COMMIT below).` — wildplasser, Apr 08 '16 at 18:47
"By delimiting... I can't schedule this script in cron" - sorry, I'm having trouble understanding this. Why would cron be affected by what was in an .sql script file? — Richard Huxton, Apr 08 '16 at 20:32
@RichardHuxton I have an script that I want to schedule with `cron`, this script builds a few temp tables and then runs queries against those temp tables. If I use a use the `;` termination, the script will only execute the first query (first temp table), none of the subsequent temp tables / resulting queries will be run — skilbjo, Apr 08 '16 at 20:37
@wildplasser that's not an issue for me...I use a global temp table as well, or adhoc table... the temp tables being dropped is not an issue. If I can get the answer to the question `select 1 select 2` in the same PGexec call, I can accomplish what I'm after — skilbjo, Apr 08 '16 at 20:39
But why do you use libpq / embedded SQL? You can submit .SQL scripts via `psql mydb < — wildplasser, Apr 08 '16 at 20:42
I'd rather just use 100% embedded SQL, and also not use `psql`. I have the same functionality with `t-sql`, at it seems like with some syntax I could get the same functionality with `postgres` — skilbjo, Apr 08 '16 at 20:48
And how are you going to handle these silly `@params` and `[TableNames]` ? Deal with it: they are different. — wildplasser, Apr 08 '16 at 20:52
What if you use `begin...end` rather than `begin....commit` in your `select 1` `select 2` above? I tried it with `Psequel` and no table was returned, but no error was announced. — user177196, Jan 05 '22 at 19:22

wildplasser · Answer 1 · 2018-11-28T18:35:17.180

3

You don't need libpq directly, you can just use he psql front end (in cron, you might need to specify the absolute pathname for the binary)

#!/bin/sh
psql -U my_user mydb <<OMG
  begin;
  select tralal 1;
  commit;

  begin;
  select domtidom 2;
  commit;
OMG

edited Nov 28 '18 at 18:35

answered Apr 08 '16 at 20:47

wildplasser

43,142
8
66
109

Any way I could just use 100% embedded SQL for this, and not use the `psql` tool? For example, use Postico GUI (or pgAdmin III) in a query window to issue both `select 1 select 2` wrapped in whatever needed syntax to get both queries executed on the server? (& results from the first query don't have to be returned back to client) – skilbjo Apr 08 '16 at 20:52
Yes, you can builtd a frontend, based on libpq. Just filter out the comments, find the next `;` and submit (beware of strings!) And retrieve the results and throw them away.. Passing parameters to prepared queries will be a bit more difficult. But all this is basically just exactly what `psql dbname /dev/null` does. – wildplasser Apr 08 '16 at 20:58
And if you want to allow multiple statements **WITHOUT** `;` termination inbetween, you'll need some minimal kind of parser (SQL is not LL(1), so this will not be a pretty task. Maybe a 3rd order Markov-filter could work) Good luck! – wildplasser Apr 08 '16 at 21:35

skilbjo · Accepted Answer · 2016-04-08T22:36:25.207

I was able to accomplish what I was looking for with CTEs rather than temp tables... one long chain of CTEs (acting as temp tables) waterfalling into the main query.

A simple example:

with first as (
    select 1 as col
),
second as (
    select 2 as col
)
select * from first union all select * from second

A more complex example:

with COGS as (
    select 'Product1' Vertical, 3.0 Credit, 1.00 Debit, 2.75 Blend, 4.30 Amex,  0.25 ACH union
    select 'Product2',   3.1,   2.2,    2.8,    4.5,    0.25    union
), 
Allocable_Card_Volume as (
    select MPR.Date, sum(MPR.Card_Volume_Net_USD)  Allocable_Card_Volume
    from  mpr_base MPR  
    where MPR.Gateway in ('YapProcessing') and MPR.Vertical not in ('HA-Intl','HA')
    group by MPR.Date
),
COGS_Financials_Base as (
    select '2013-01-31'::DATE Date , 1000 Total_COGS , 200 Homeaway , (select Allocable_Card_Volume from Allocable_Card_Volume where Date in ('2013-01-31') ) Allocable_Card_Volume union
),
Initial_COGS as (
    select
        MPR.Date,
        sum(
        case    when    MPR.PaymentTypeGroup in ('ACH_Scan','AmEx') then (Txn_Count * COGS.ACH) else 0 end +
        case    when    MPR.Vertical not in ('HA') and MPR.PaymentTypeGroup in ('Card','AmEx-Processing') then
                                coalesce( ((Credit_Card_Net_USD - Amex_Processing_Net_USD) * COGS.Credit * 0.01),0) + coalesce((Debit_Card_Net_USD * COGS.Debit * 0.01),0) + coalesce((Amex_Processing_Net_USD * COGS.Amex * 0.01),0) + coalesce((case when TPV is null and PaymentTypeGroup in ('Card') then TPV_Billing else 0 end * COGS.Blend * 0.01),0)
                    when    MPR.Vertical in ('HA') and MPR.PaymentTypeGroup in ('Card','AmEx-Processing') and FeePaymentType in ('PropertyPaid') then
                                coalesce(COGS_Financials.Homeaway,0)
                                else 0 end
        ) Initial_COGS
    from
        mpr_base MPR
        left join COGS on COGS.Vertical = MPR.Vertical and MPR.Gateway in ('YapProcessing') and MPR.PaymentTypeGroup not in ('Cash')
        left join COGS_Financials_Base COGS_Financials on MPR.Date = COGS_Financials.Date and MPR.Gateway in ('YapProcessing') and MPR.PaymentTypeGroup in ('Card')
    where MPR.Gateway in ('YapProcessing') and MPR.Vertical not in ('HA-Intl') and MPR.PaymentTypeGroup not in ('Cash')
    group by
        MPR.Date
),
COGS_Financials as (
    select
        COGS_Financials_Base.*, (COGS_Financials_Base.Total_COGS - Initial_COGS.Initial_COGS) Allocation
    from
        COGS_Financials_Base
        join Initial_COGS on COGS_Financials_Base.Date = Initial_COGS.Date
),
MPR as (
    select
        MPR.Date,MPR.Gateway,MPR.Vertical, MPR.ParentAccountId, MPR.ParentName ,
        MPR.PaymentTypeGroup ,
        sum(TPV_USD) TPV_USD,
        sum(TPV_Net_USD) TPV_Net_USD,
        sum(Revenue_Net_USD) Revenue_Net_USD ,
        sum(coalesce(
            case    when MPR.PaymentTypeGroup in ('ACH_Scan','AmEx') then (Txn_Count * COGS.ACH) else 0 end +
            case    when MPR.Vertical not in ('HA') and MPR.PaymentTypeGroup in ('Card','AmEx-Processing') then
                    coalesce( ((Credit_Card_Net_USD - Amex_Processing_Net_USD) * COGS.Credit * 0.01),0) + coalesce((Debit_Card_Net_USD * COGS.Debit * 0.01),0) + coalesce((Amex_Processing_Net_USD * COGS.Amex * 0.01),0) + coalesce((case when TPV is null and PaymentTypeGroup in ('Card') then TPV_Billing else 0 end * COGS.Blend * 0.01),0)
                +(coalesce( ( ( cast(Card_Volume_Net_USD as decimal(18,2) ) / cast(COGS_Financials.Allocable_Card_Volume as decimal(18,2)) ) * COGS_Financials.Allocation  ), 0) ) -- Excess
                        when MPR.Vertical in ('HA') and MPR.PaymentTypeGroup in ('Card','AmEx-Processing') and MPR.FeePaymentType in ('PropertyPaid') then  coalesce(COGS_Financials.Homeaway,0)
                        else 0
          end,0)
        ) COGS_USD,
        sum(Txn_Count) Txn_Count
    from
        mpr_Base MPR
        left join COGS on COGS.Vertical = MPR.Vertical and MPR.Gateway in ('YapProcessing') and MPR.PaymentTypeGroup not in ('Cash')
        left join COGS_Financials on MPR.Date = COGS_Financials.Date and MPR.Gateway in ('YapProcessing') and MPR.PaymentTypeGroup in ('Card','AmEx-Processing')
    where
        MPR.Date in ('2016-02-29')
    group by
        MPR.Date,MPR.Gateway,MPR.Vertical  , MPR.ParentAccountId ,MPR.ParentName,
        MPR.PaymentTypeGroup 
)
select
    Vertical, 
    sum(TPV_USD)::money as TPV_USD,
    sum(Revenue_Net_USD)::money as Revenue_Net_USD,
    sum(COGS_USD)::money COGS_USD,
    round((sum(Revenue_Net_USD)-sum(COGS_USD))/sum(Revenue_Net_USD)*100,2) Accounting_Margin
from 
    MPR
where Date in ('2016-02-29')
group by
    Vertical
union all
select
    'Total' , 
    sum(TPV_USD)::money as TPV_USD,
    sum(Revenue_Net_USD)::money as Revenue_Net_USD,
    sum(COGS_USD)::money COGS_USD,
    round((sum(Revenue_Net_USD)-sum(COGS_USD))/sum(Revenue_Net_USD)*100,2) Accounting_Margin
from 
    MPR
where Date in ('2016-02-29')

I said it would be complex :-)

This is not complex. This is an overly complex *construct* . — wildplasser, Apr 08 '16 at 23:53

score 2 · Answer 3 · answered Nov 28 '18 at 09:48

2

From your answer, you could also do this

SELECT * FROM a
 UNION ALL
SELECT * FROM b
 UNION ALL
SELECT * FROM c
...

answered Nov 28 '18 at 09:48

Kevin Potgieter

672
10
23

Pass multiple postgres SQL statements in a single PGexec call

3 Answers3