I've got a function in PostgreSQL / plpgsql using the following signature:
CREATE OR REPLACE FUNCTION user_login(TEXT, TEXT) RETURNS SETOF _get_session AS $$ ... $$
Where _get_session is really a view. The function works fine when calling it from phpPgAdmin, however whan I refer to it as from PHP I recieve the next error:
Warning: pg_query() [function.pg-query]: Query unsuccessful: ERROR: type "session_ids" doesn't exist CONTEXT: compile of PL/pgSQL function "user_login" near line 2 in /home/sites/blah.com/index.php online 69
The DECLARE portion of the function consists of the next variables:
oldSessionId session_ids := $1; newSessionId session_ids := $2;
The domain session_ids DOES exist, along with other functions designed to use exactly the same domain work when known as in the same script. The PHP is the following:
$query = "SELECT * FROM $dbschema.user_login('$session_old'::TEXT, '$session'::TEXT)"; $result = pg_query($login, $query);
I've also attempted this using ::session_ids instead of ::TEXT when calling the function, however recieve exactly the same error.
Simply make your code simple:
$query = "SELECT * FROM $dbschema.user_login($1, $2)"; $result = pg_query_params($login, $query, array($session_old, $session));
Now you are protected from SQL injection.
But, your function continues to be wrong, there's no datatype "session_ids". I believe you need to use TEXT within the DECLARE part.
In case your query covers multiple lines, then PHP is probably not delivering them included in the same transaction. If this sounds like the situation you've two options.
The very first choice is to transmit all of the queries within the same call
pg_query("query1; query2; query3;");
The 2nd option (and also the best for me) is by using transactions. This will help you to result in the calls over several lines although the begin statement will in all probability have to be sent using the initial query.
pg_query("begin; query1;"); pg_query("query2;"); pg_query("commit;");
If there's a mistake that happens, then replace the commit having a rollback, with no changes may have occurred towards the db.
Whenever using Postgres, this really is really a great guideline to follow along with anyway.