Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2010
    Posts
    4

    Question Unanswered: Sort Issue on PostgreSQL return

    Anyone run across this issue before?

    I created a postgresql stored procedure that returns the rows corresponding to its defined query. (Note that 'tpipes' is a type that I defined consisting of the columns i wish returned)

    CREATE OR REPLACE FUNCTION get_tpipe_by_member_asc(bigint, character varying, bigint, bigint)
    RETURNS SETOF tpipes AS
    'select id,tpipe_title,tpipe_cat,tpipe_cow from t_tpipes where member_id = $1 order by $2 asc offset $3 limit $4;'
    LANGUAGE 'sql' VOLATILE;

    when performing the stored procedure manually at the pgsql db command line (select * from get_tpipe_by_member_asc(...)), the results return correctly sorted. However when attempting to call the stored procedure within php, the rows are returned unsorted as if the return rows are somehow being altered before being made available in the return php variable (in the case below, $tpipes_array:

    1. $tpipes_array = pg_query_params ($conn, "SELECT * from get_tpipe_by_member_".$tpipe_asc."($1,$2,$3,$4)", array ($member_id,$tpipe_sort_key,$tpipe_offset,$tpipe_l imit));

    2. $tpipes_array=pg_query($conn,"select id,tpipe_title,tpipe_cat,tpipe_cow from t_pipes where member_id = ".$member_id." order by ".$tpipe_sort_key." ".$tpipe_asc." offset ".$tpipe_offset." limit ".$tpipe_limit);

    Note #1 is the line used to call the stored procedure, which results in the unsorted rows.

    In #2, we are calling the query explicitly (avoiding stored procedure all together) and this results in correctly ordered rows. How is this possible? Anyone have any thoughts?

    thed00d@nesornet

  2. #2
    Join Date
    May 2008
    Posts
    277
    I'm actually surprised this is working at all. From the documentation:

    arguments can only be used as data values, not as identifiers.
    So you have two options:

    1) Use a PGSQL function and dynamically construct the statement:
    Code:
    execute '
        select id,tpipe_title,tpipe_cat,tpipe_cow 
        from t_tpipes 
        where member_id = ' || quote_literal($1) || '
        order by ' || quote_ident($2) || ' asc 
        offset ' || quote_literal($3) || ' 
        limit ' || quote_literal($4);
    2) Execute the statement directly, like in your second example (while properly escaping any untrusted input, of course).

    My personal preference would tend towards the second option

  3. #3
    Join Date
    Sep 2010
    Posts
    4

    Smile

    Thanks for the doc reference! I'm actually a little surprised by this constraint, but if I can trade an adjustment for stability I'm all for it.

    2 questions:

    1. Are you referring to the use of 'execute' within pl/pgsql?
    I've never used 'execute' function before, but google attempts at researching this topic so far seem to indicate that the use of 'execute' is valid in pl/pgsql and perhaps not exclusively to it. Is this correct? Do you know of any good doc resources detailing 'execute'?

    Just as a side note: on a handful of my stored procedures I opted for the use of 'sql' over 'pl/pqsql' when needing to return multiple rows (in contrast to a single row or single value return). I determined this after researching and finding out that returning multiple rows in 'pl/pgsql' looked a bit more complex compared to the seemingly simple 'sql' solution. I am new to pgsql in general, so my conclusions into this may have been inaccurate. If you were implying the use of 'execute' within pl/pgsql, I'm curious as to whether this would simplify the multiple row solution I had looked into previously.

    2. Could you explain a bit more as to why your preference lies in working with the query statement directly, over the stored procedure?

  4. #4
    Join Date
    May 2008
    Posts
    277
    Quote Originally Posted by nesornet View Post
    1. Are you referring to the use of 'execute' within pl/pgsql?
    Yes. As far I know it, it can only be used within a pl/pgsql function. Here's the documentation.

    2. Could you explain a bit more as to why your preference lies in working with the query statement directly, over the stored procedure?
    In general, I'm not a fan of using functions to provide what generally amounts to relatively straight-forward read/write access to the database. All you're basically doing is adding the overhead of a stored procedure, and in this case, all it's doing is creating the same dynamic sql you could be creating and executing directly from your client application.

Posting Permissions

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