#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Location
    Calgary, Canada
    Posts
    28
    Rep Power
    0

    Problems porting function (MS --> Postgres)


    I am attempting to convert a SQLServer 2000 DB to Postgres. My company has kindly donated DB space to me, but I have a new computer and think it is time to move it to my house. While the data import worked alright, I have a bunch of functions defined that need to be recoded for Postgres.

    If I want to return a "setof" data, what datatype would I declare the variable I select into?

    This is my first attempt at writing a function in PostgreSQL. All of the other functions in the database are based on this function. Currently, when I run this function, I get the error:
    Code:
    NOTICE:  Error occurred while executing PL/pgSQL function fportpos
    NOTICE:  line 10 at SQL statement
    ERROR:  SELECT query has no destination for result data.
           If you want to discard the results, use PERFORM instead.
    When I first started to write fportpos, I was using "language SQL", but have since switched to "language plpgsql". I did this so that I can do some error checking on the data that is passed in. This got me a lot further into the process but now I am supposed use the keyword "return". If I want to return a "setof" data, what datatype would I declare the variable I select into?


    Code:
    CREATE or REPLACE function fportpos(date)
    RETURNS setof text
    AS '
    DECLARE
     dt date;
    BEGIN
    
     dt:= $1;
     if (dt is null) then
       dt:= current_date();
     end if;
    
     SELECT p.port,
            p.exch,
            p.org,
            p.secid,
            SUM(trade.units) AS Pos,
            case when d.closeprice is null then trade.price else d.closeprice end AS price,
            SUM(trade.totcost) AS totcost,
            odate,
            cdate
     FROM   tposition p
            INNER JOIN ttransactions trans ON p.port = trans.port AND
                                              p.oDate <= trans."date" AND
                                              trans."date" <= dt
            INNER JOIN ttrades trade       ON trans.transid = trade.transid AND
                                              p.exch = trade.exch AND
                                              p.org = trade.org AND
                                              p.secid = trade.secid
            LEFT OUTER JOIN tsecdaily d    ON p.secid = d.secid AND
                                              p.exch = d.exch AND
                                              p.org = d.orgid
     WHERE  (p.cdate IS NULL OR p.cdate >= dt) AND
            (p.odate <= dt) AND
            (d."date" IS NULL OR d."date" = (SELECT MAX("date")
                                             FROM   tsecdaily
                                             WHERE  p.exch = exch AND
                                                    p.org = orgid AND
                                                    p.secid = secid AND
                                                    "date" <= dt))
     GROUP BY p.port,
              p.exch,
              p.org,
              p.secid,
              case when d.closeprice is null then trade.price else d.closeprice end,
              p.odate,
              p.cdate
     ORDER BY p.port,
              p.exch,
              p.org,
              p.secid,
              case when d.closeprice is null then trade.price else d.closeprice end,
              p.odate,
              p.cdate;
    
     return 1;
    END' Language 'plpgsql';
    I think I'm on the right track...
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    373
    Rep Power
    12
    ok, here's what the manual says:

    When a PL/pgSQL function is declared to return SETOF sometype, the procedure to follow is slightly different. In that case, the individual items to return are specified in RETURN NEXT commands, and then a final RETURN command with no arguments is used to indicate that the function has finished executing. RETURN NEXT can be used with both scalar and composite data types; in the later case, an entire "table" of results will be returned. Functions that use RETURN NEXT should be called in the following fashion:

    SELECT * FROM some_func();

    That is, the function is used as a table source in a FROM clause.

    RETURN NEXT expression;

    RETURN NEXT does not actually return from the function; it simply saves away the value of the expression (or record or row variable, as appropriate for the data type being returned). Execution then continues with the next statement in the PL/pgSQL function. As successive RETURN NEXT commands are executed, the result set is built up. A final RETURN, which need have no argument, causes control to exit the function.

    Note: The current implementation of RETURN NEXT for PL/pgSQL stores the entire result set before returning from the function, as discussed above. That means that if a PL/pgSQL function produces a very large result set, performance may be poor: data will be written to disk to avoid memory exhaustion, but the function itself will not return until the entire result set has been generated. A future version of PL/pgSQL may allow users to allow users to define set-returning functions that do not have this limitation. Currently, the point at which data begins being written to disk is controlled by the SORT_MEM configuration variable. Administrators who have sufficient memory to store larger result sets in memory should consider increasing this parameter.
    This should be able to get you through your issue.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    373
    Rep Power
    12

    Re: Problems porting function (MS --> Postgres)


    I'm going to make some changes to the way you wrote this that may also help.

    Originally posted by kavius
    [B
    Code:
    NOTICE:  Error occurred while executing PL/pgSQL function fportpos
    NOTICE:  line 10 at SQL statement
    ERROR:  SELECT query has no destination for result data.
           If you want to discard the results, use PERFORM instead.
    Code:
    CREATE or REPLACE function fportpos(date)
    RETURNS setof text
    AS '
    DECLARE
     dt date;
    
    --LOOKIE I ADDED STUFF
    refcursor1 refcursor;
    some_row_type bla%ROWTYPE; --for you to fetch into
    --DONE LOOKIE
    
    BEGIN
    
     dt:= $1;
     if (dt is null) then
       dt:= current_date();
     end if;
    
    --what you're trying to do here is called a refcursor, where you 
    --build the cursor based on your current state... it has its 
    --own kind of declaration
    
    IF d.closeprice IS NULL THEN 
             
             OPEN refcursor1 FOR  SELECT ... trade.price AS price ... GROUP BY ... trade.price ... ORDER BY ... trade.price ...;
             FETCH refcursor1 INTO some_row_type;
             
    ELSE 
             OPEN refcursor1 FOR SELECT ... d.closeprice AS price ... GROUP BY ... d.closeprice ... ORDER BY ... d.closeprice ...;
             FETCH refcursor1 INTO some_row_type;
    END IF;
    
    CLOSE refcursor1;
    
     --SELECT p.port,
      --      p.exch,
      --      p.org,
      --      p.secid,
      --      SUM(trade.units) AS Pos,
      --      case when d.closeprice is null then trade.price else --d.closeprice end AS price,
     --       SUM(trade.totcost) AS totcost,
     --     GROUP BY p.port,
     --         p.exch,
     --         p.org,
     --         p.secid,
     --         case when d.closeprice is null then trade.price else --d.closeprice end,
      --        p.odate,
     --         p.cdate
     --ORDER BY p.port,
      --        p.exch,
      --        p.org,
      --        p.secid,
      --        case when d.closeprice is null then trade.price else --d.closeprice end,
     --         p.odate,
     --         p.cdate;
    
     return 1;
    END' Language 'plpgsql';
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    373
    Rep Power
    12
    do you see what I was getting at?? kind of in a hurry...

    another thing I don't get is what is this set of text you're returning??? why don't you just store it in another relation or something, forget about returning it; besides, you've got
    RETURN 1; so I don't know where you planned on returning the set?
  8. #5
  9. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Location
    Calgary, Canada
    Posts
    28
    Rep Power
    0
    Thanks. It's going to take me a while to go through this (looking for the reference in the manual right now).

    I've done this a million times in msSQL, but am having a great time learning how to do it in postgreSQL.

    the setof text was just from what the manual had in the sample. I'm not entirely sure what I want to return (though I suspect now that I am trying to get a recordset back, I just don't know how to define that).

    The return 1... even I'm not sure why I did that.

    The reason this function exists was because I had a view that I ran at the end of every day that told me the position of my portfolio and its current value. I noticed that I had made a clerical error in my data about 4 months ago, I went back and fixed it, but that meant that the spreadsheet I am using to graph the daily value was way out. I needed a way to get the daily report on historical data.

    The solution seemed to be to create functions that mimic the views, but took a date parameter.

    One of the reasons I chose postgres as my new DB was because it has support for stuff like this (and I can get it to run on my linux box at home for free).

    Thanks for the help, you have probably given me enough to chew on for a couple of days (doing this over my lunch hour for now).
    Last edited by kavius; November 13th, 2003 at 04:19 PM.
  10. #6
  11. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Location
    Calgary, Canada
    Posts
    28
    Rep Power
    0
    Alright, I think I am making real progress on this now. Thanks metaBarf. You got me reading the right documentation. I came across a good article on postegreSQL's website while looking up the reference you gave me:
    http://techdocs.postgresql.org/guide...rningFunctions

    Here is my solution as it sits now:
    Code:
    -- define the row type that my recordset will be using
    -- where metaBarf was wondering why I was returning 
    -- a setof text, this is what I should be returning
    create type typeportpos as(
      port    int, 
      exch    int,
      org     int,
      secid   int,
      pos     numeric(7,4),
      price   numeric(7,4),
      totcost numeric(10,4),
      odate   date,
      cdate   date
    );
    My current problem resides with the creating of a new type. I have followed the syntax that I can find in the documentation, but for some reason I keep getting an error:
    Code:
    ERROR:  parser: parse error at or near "as"
    I cannot see any difference between what I am doing and what is being done in the samples. I even tried the samples and got the same error. I am starting to suspect that this may have something to do with my version (though that doesn't make sense to me). I am using 7.2, is the "create type" only available in 7.3?

    I'll just keep looking stuff up until I see a reference that says I can't do that.
    Code:
    CREATE or REPLACE function fportpos(date)
    RETURNS setof typeportpos AS '
    DECLARE
      dt date;
      r typeportpos%ROWTYPE; 
      cportpos refcursor;
    
    BEGIN
      --error check that there is a date (if no date, grab now)
      dt:= $1;
      if (dt is null) then
        dt:= current_date();
      end if;
    
      -- select all the data that describes our current position.
      -- METABARF: I considered your idea for using an if 
      -- statement around two different cursor calls, but I don't
      -- think it will work. Each row potentially has a null closeprice,
      -- not the entire dataset, so I have to do the comparison on a 
      -- row by row basis.
      OPEN cportpos FOR
      SELECT p.port,  ...  cdate
      FROM   tposition p ...
      WHERE  ...;
      
      -- build a returnable recordset
      for r in cportpos
        return next r
      end loop;
      
      return;
    END' Language 'plpgsql';
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    373
    Rep Power
    12
    yea, the reference I gave you went to the 7.3 documentation. you probably already found it but here's the 7.2 documentation for CREATE TYPE

    to me, the docs are clearer for 7.3 where it shows with the synopsis:
    CREATE TYPE typename ( INPUT = input_function, OUTPUT = output_function
    , INTERNALLENGTH = { internallength | VARIABLE }
    [ , DEFAULT = default ]
    [ , ELEMENT = element ] [ , DELIMITER = delimiter ]
    [ , PASSEDBYVALUE ]
    [ , ALIGNMENT = alignment ]
    [ , STORAGE = storage ]
    )

    CREATE TYPE typename AS
    ( column_name data_type [, ... ] )
    see how there are actually 2 CREATE TYPE
    statements here? I think you have to make a template/handler function for the type first, then make the explicit type. I will read up more and give you a more detailed answer.
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    373
    Rep Power
    12
    from 7.3 docs

    Composite Types

    The second form of CREATE TYPE creates a composite type. The composite type is specified by a list of column names and data types. This is essentially the same as the row type of a table, but using CREATE TYPE avoids the need to create an actual table when all that is wanted is to define a type. A stand-alone composite type is useful as the return type of a function.
    from 7.3 docs... you can upgrade, just use pg_dump. I know editing the config files is probably a pain but it'll help you do the problem the way you wish to.

    This means in 7.2 you can just make a scalar. BUT


    I keep wondering though if you want to do this a different way. If you have making a "type" of distinct values, then why wouldn't you just make a relation that consists of these types in the same way as the type?
  16. #9
  17. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Location
    Calgary, Canada
    Posts
    28
    Rep Power
    0
    Sorry for taking so long, don't have internet at home right now (just moved) and missed your message befor the weekend.

    Hmmm... Can't be done in 7.2...

    This changes a couple of things then. Currently, I am using 7.2 because I could get a native build of that for windows.

    Maybe I will get 7.3 installed at home and build the functions there. When I get them working and debugged, I will do a dump at work, take the data home, bring the data in at home, and then create the functions.

    That puts this on hold for a while. I just moved, computer at home is having over heating issues, and I don't have an internet connection at home yet.

    Once I get my comp at home working I will revisit this problem.
    If you have making a "type" of distinct values, then why wouldn't you just make a relation that consists of these types in the same way as the type?
    Alright, you have me intrigued... and confused. I'm not entirely sure what you mean by this. This DB has never been seen by anyone else before. Its my own little attempt at scratching an itch I had about a year ago, so I am definately open to suggestions on how to make it more efficient.

    Just to makes sure: "relation" is synonymous with "table"? That is my impression from reading docs, but I have never actually confirmed that.

    If so, I am not sure that this is solution would be optimal. It's not so much that this is "distinct" values I am looking for, its that this is a report that is time sensitive. The database tracks an investment portfolio. The idea is to give the position of the portfolio at a given date. I do have a summary table that keeps most of this information in it and its a simple view to get all of this data for the current date.

    The problem is if I want to get a snapshot of the data as it was a week or a year ago. In that case I have to lookup what shares I owned on that day, how many of them I owned, and what their value was. Since the price of securities change almost daily, I would need to add a rows everyday. I just think the data storage requirements would get out of hand, especially since it doesn't take very long to rebuild the data by using the partially summarized data I do maintain.

    The last point about the amount of data this table would need is that it currently tracks 2 portfolios (mine, and my computer's), and I am planning on adding 2 more on Jan 1 (my girlfriend's, and another computer test). That means ~8 rows per day for data that rarely gets accessed (I don't generally look at everyday, just particular points in time). Having said that, there is one argument for having all of the data precalculated for everyday (but I am going to hold that argument back for now), it just hasn't been an issue yet. It only becomes an issue if soneone other than me starts using it (my girlfriend I being that person).

    Now if by "relation" you mean something totally different, completely disregard the previous points and do explain. If you need more information, feel free to ask.

IMN logo majestic logo threadwatch logo seochat tools logo