Results 1 to 2 of 2
  1. #1
    Join Date
    May 2014
    Posts
    16

    Unanswered: Creating Postgressl to return results for multiple queries

    I'm trying to figure out how to get a store procedure in postgresql to return the results for multiple scripts. I could achieve this in MSSQL Server, but having a hard time understanding how this works in postgresql with functions.

    I want to create a stored procedure that returns me a result set for these two scripts belowhow would I achieve that?)

    1>) select sg_con_d4.sg_con4_contract, sg_con_d4.sg_con4_seq, sg_con_d4.sg_con4_posted, sg_con_d4.sg_con4_postdate, sg_con_d4.sg_con4_locked, sg_con4_lockdate,
    sg_con_d4.* from sg_con_d4
    JOIN sg_con_m1 ON sg_con_m1.sg_con_contract = sg_con_d4.sg_con4_contract
    where sg_con4_plc = ANY (ARRAY['HYCP'::character varying::text, 'HYTC'::character varying::text, 'HYVS'::character varying::text, 'HYGP'::character varying::text, 'HYPM'::character varying::text, 'HYTW'::character varying::text, 'HYDD'::character varying::text, 'HYUP'::character varying::text, 'HYTP'::character varying::text, 'HYAP'::character varying::text, 'HYKR'::character varying::text, 'HYLS'::character varying::text, 'HYCO'::character varying::text, 'HYLG'::character varying::text])
    AND sg_con_d4.sg_con4_rectype <> '2'::bpchar
    AND (sg_con_d4.sg_con4_busdate >= '2013-01-01'::date and sg_con_d4.sg_con4_busdate <= (current_date -1)::date)
    AND sg_con_d4.sg_con4_posted = 'y'::text
    AND (sg_con_d4.sg_con4_locked = '' or sg_con_d4.sg_con4_locked is null)


    2>) select sg_con_d4.sg_con4_contract, sg_con_d4.sg_con4_seq, sg_con_d4.sg_con4_posted, sg_con_d4.sg_con4_postdate, sg_con_d4.sg_con4_locked, sg_con4_lockdate,
    sg_con_d4.* from sg_con_d4
    JOIN sg_con_m1 ON sg_con_m1.sg_con_contract = sg_con_d4.sg_con4_contract
    where sg_con4_plc = ANY (ARRAY['HYCP'::character varying::text, 'HYTC'::character varying::text, 'HYVS'::character varying::text, 'HYGP'::character varying::text, 'HYPM'::character varying::text, 'HYTW'::character varying::text, 'HYDD'::character varying::text, 'HYUP'::character varying::text, 'HYTP'::character varying::text, 'HYAP'::character varying::text, 'HYKR'::character varying::text, 'HYLS'::character varying::text, 'HYCO'::character varying::text, 'HYLG'::character varying::text])
    AND sg_con_d4.sg_con4_rectype <> '2'::bpchar
    AND (sg_con_d4.sg_con4_busdate >= '2013-01-01'::date and sg_con_d4.sg_con4_busdate <= (current_date -1)::date)
    AND sg_con_d4.sg_con4_posted = 'y'::text
    AND (sg_con_d4.sg_con4_locked = '' or sg_con_d4.sg_con4_locked is null)

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Do you want the results as two separate record sets, or as one recordset?

    If the latter, simply add a "UNION ALL between the two scripts

    Select foo from bar where foo = 2

    UNION ALL

    Select bar from foo where bar < 0;

    The data types in each column position must be the same (or be able to be cast as the same type.) In the event of column names being different, the first is used.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •