Question

2

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(

edited Apr 19 at 16:07

asked Apr 19 at 15:58

CitrusTree
18●3

	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? :\| – Frank Farmer Apr 19 at 16:11
	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( – CitrusTree Apr 19 at 16:14

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

0

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 at 16:16

Frank Heikens
1,670●1●7

	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 at 16:18
	Are you 100% sure you're using the same database for your script and pgAdmin? – Frank Heikens Apr 19 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 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 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 at 19:08

show 2 more comments

PHP calling PostgreSQL function - type issue?

1 Answer

Your Answer

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

Hello World!

Related