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(

Answer 1

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.

Answer 2

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.