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

I'm actually surprised this is working at all. From the documentation:

So you have two options:

1) Use a PGSQL function and dynamically construct the statement:
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

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?

Yes. As far I know it, it can only be used within a pl/pgsql function. Here's the documentation.

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.