Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2005
    Location
    Singapore
    Posts
    60

    Unanswered: Stored Procedure can behave as TABLE ?

    I'm interested in OpenSource DB. Currently I'm using Sybase SQLAnywhere.
    I have several questions to ask.

    Can I use following statement in PostGreSQL ?

    1. Derived Table
    select x,y,z from (select a,b,c from emp) test(x,y,z)

    2. Common Table
    with test(e_code,e_name,amt) as (select a,b,c from emp)

    3. Recursive SQL
    PROCEDURE gen_no"(fr_no integer,to_no integer)
    begin
    with recursive num(nu) as
    (select fr_no
    union all
    select nu+1 from num where nu <= to_no
    )
    select nu from num;
    end

    select * from gen_no(23,33) where g_no not in (select do_no from
    do_hdr where do_no between 23 and 33)

    4. Stored Procedure behave as table
    this is stored procedure sp_invoice_summary(yr,mth)
    yr=2007,mth=2

    select a.cust_code,sum(b.amt) from sp_invoice_summary(yr,mth) a,inv b
    where a.cust_code=b.cust_code and invdate > today() - 7
    group by a.cust_code

    Many Thanks in advanced

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    I would suggest downloading a copy of the latest version of PostgreSQL (v8.3) and take it for a spin. It's free, after all.

    It's pretty easy to use subqueries in posgresql - just be sure to properly alias the from clauses.

    For instance

    select b.start_date from (Select * from dates a) b

    works just fine.

    But you couldn't expect to select fields x, y, and z from a table as a subquery, and then select fields a, b, anc c from that subquery. Those fields weren't pulled out of the table with the subquery.


    Stored functions can return sets of records.

    And, stored functions may be recursive.
    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


  3. #3
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by loquin
    I would suggest downloading a copy of the latest version of PostgreSQL (v8.3)
    The latest version is 8.2.3, not 8.2

  4. #4
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Quote Originally Posted by shammat
    The latest version is 8.2.3, not 8.2
    ok. I slipped a digit. the gist is still the same. (and, as long as we're getting persnickety, I said 8.3... )
    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


  5. #5
    Join Date
    Jun 2005
    Location
    Singapore
    Posts
    60
    Question for Stored Procedure as table is

    I have made stored procedure and I execute it with table such as
    sp_wms has one arguement expired date, return cust_code,parts,loc,qty
    cust is customer master cust_code,cust_name

    select * from sp_wms('2005/12/31') a,cust b where a.cust_code=b.cust_code

    so that I can execute stored procedure using select statement to get
    expired parts list

    I think SQLServer2000 can't execute it how about PostGreSQL 8.2.3 ?

Posting Permissions

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