PHP calling PostgreSQL function - type issue?

Question

I have a function in PostgreSQL / plpgsql with the following signature:

CREATE OR REPLACE FUNCTION user_login(TEXT, TEXT) RETURNS SETOF _get_session AS $$ ... $$

Where _get_session is a view. The function works fine when calling it from phpPgAdmin, however whan I call it from PHP I get the following error:

Warning: pg_query() [function.pg-query]: Query failed: ERROR: type "session_ids" does not exist CONTEXT: compile of PL/pgSQL function "user_login" near line 2 in /home/sites/blah.com/index.php on line 69

The DECLARE section of the function contains the following variables:

oldSessionId session_ids := $1;
newSessionId session_ids := $2;

The domain session_ids DOES exist, and other functions which use the same domain work when called from the same script. The PHP is as follows:

$query = "SELECT * FROM $dbschema.user_login('$session_old'::TEXT, '$session'::TEXT)";
$result = pg_query($login, $query);

I have also tried this using ::session_ids in place of ::TEXT when calling the function, however I recieve the same error.

Help :o(

I'm gonna guess that pg_query can't parse arbitrary return types -- it'll do fine with TEXT, INT, etc., but may not know what to do with a view. Have you tried "return setof record" instead? :|
Thanks, I've not, but the same principle works with every other function in the database. There are 4 in total which all return SETOF _whatever_view. Really odd. The other difference is that this function is run by a different user to the others (which has permission to run the views involved). I can't work it out :o(

Frank Heikens · Answer 1 · 2010-04-19 16:16:09Z

up vote 1 down vote

Just make your code simple:

$query = "SELECT * FROM $dbschema.user_login($1, $2)";
$result = pg_query_params($login, $query, array($session_old, $session));

Now you're safe from SQL injection.

But, your function is still wrong, there is no datatype "session_ids". I think you want to use TEXT in the DECLARE part.

answered Apr 19 '10 at 16:16

Frank Heikens
18.3k32135

1

Thanks for the tip - I'll definitly do that. There IS a session_ids domain though - and it;s used in the other function :o( – CitrusTree Apr 19 '10 at 16:18

Are you 100% sure you're using the same database for your script and pgAdmin? – Frank Heikens Apr 19 '10 at 16:49

Fair question... Yes, all the other bits are working just fine (on the same request/response) - it's only this function that's causing the issue. – CitrusTree Apr 19 '10 at 17:23

Is the "session_ids" domain in an accessible schema? Maybe you need to qualify it if its schema is not in your "search_path"? – Milen A. Radev Apr 19 '10 at 18:21

Thanks Milen. PostgreSQL is new to me (very - a few days), so it may well be that. I'm not used to qualifying schemas and not 100% sure how long it would stay in scope for a given session / connection. What I can say is that an earlier query in the same script is: $result = pg_query($db, "SET search_path TO $dbschema;"); What about users? Do users need premissions over domains as well as views? I can't find anything to imply this is the case... – CitrusTree Apr 19 '10 at 19:08

show 2 more comments

feedback

Joseph · Answer 2 · 2010-05-19 15:38:10Z

If your query covers multiple lines, then PHP is most likely not sending them as part of the same transaction. If this is the case you have two options.

The first option is to send all the queries in the same call

pg_query("query1; query2; query3;");

The second option (and the best in my opinion) is to use transactions. This will allow you to make the calls over several lines though the begin statement will most likely need to be sent with the initial query.

pg_query("begin; query1;");
pg_query("query2;");
pg_query("commit;");

If there is an error that occurs, then replace the commit with a rollback, and no changes will have been made to the db.

When working with Postgres, this is actually a good rule of thumb to follow anyway.

Thanks. I worked out the issue I was having already, but that's some interesting advice none the less.

asked	2 years ago
viewed	1012 times
active	2 years ago

PHP calling PostgreSQL function - type issue?

2 Answers

Your Answer

Not the answer you're looking for? Browse other questions tagged php sql database postgresql plpgsql or ask your own question.

Hello World!

PHP calling PostgreSQL function - type issue?

2 Answers

Your Answer

Not the answer you're looking for? Browse other questions tagged php sql database postgresql plpgsql or ask your own question.

Hello World!

Related