Re: newbie issues with PDO / stored procedures

On Thu, 2008-06-12 at 05:46 -0400, pg 043g9j2g wrote:
> Hi! I am new to PHP and Postgres; in the early stages of creating a web
> app, just trying to achieve rudimentary results, yet trying to follow
> "best practices" -- in the form of using Stored Procedures vs. Dynamic
> SQL -- as I do hope to take it live to the scary ol' Internet one day.
>
> I have a stored procedure (function), "select_user_details", of the form
> "SELECT * FROM users;" which returns "SETOF users".
>
> When I execute my prepared statement, "SELECT
> select_user_details([userid])" via PDO, I get a rowset back which holds
> all the columns of users as an array in one column. From my experiement
> directly in pgAdmin Query tool, I guess that's what you expect when you
> return SETOF, but I already miss the simple DSQL I had set up where I
> could access returned columns via $row[columname] syntax.
>
> I messed around with using fetch() but that didn't seem right (more on
> that below). Then I took a look at fetchColumn() but I didn't like that
> because you address columns by index # instead of column name. Also the
> fact that you advance the row pointer with every call to any kind of
> fetch*() seems like it isn't well suited to retrieving data from more
> than one column.
>
> Now it looks like my best option in hopes of being able to access
> columns by semantically sensible names is to fetchObject() into PHP
> objects representing my database business objects. Is this correct?
> Suggestions? Alternatives? Insights?

I'm not sure this will work in your case, but the normal way to get
SETOF returning functions to return rows that look just like real table
rows is to define a view with the semantically sensible names and then
do your query against the view.

CREATE VIEW my_rows AS SELECT col1, col2, col3
FROM setof_returning_function();

Then you will get a normal select output in your program and be able to
refer to $row->col1 etc.

This doesn't necessarily work so well when you want to hand in a
parameter, unless you can hand that in by doing a join of some kind:

CREATE VIEW my_rows AS SELECT col1, col2, col3,
jointable.pkey_col AS other_key
FROM setof_returning_function(jointable.somecol), jointable;

Then you would need to do something like:

SELECT col1 FROM my_rows WHERE other_key = 75

Etc.

Hope this helps,
Andrew.

-------------------------------------------------------------------------
Andrew @ Catalyst .Net .NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/ PHYS: Level 6, 150-154 Willis St
DDI: +64(4)803-2201 MOB: +64(272)DEBIAN OFFICE: +64(4)499-2267
It is truth which you cannot contradict; you can without any difficulty
contradict Socrates. - Plato

-------------------------------------------------------------------------