Обсуждение: Problem either with PostgreSQL or with PHP
Hi! I have written this function in PL/pgSQL, CREATE OR REPLACE FUNCTION MakeSimpleReservation(integer, integer, integer, text, text) RETURNS boolean ... In my PHP script, I have this code: $start_ts = '2010-04-12 11:00:00'; $end_ts = '2010-04-12 14:00:00'; $update = pg_query($yhteys, "SELECT MakeSimpleReservation(2, 3, 1, '{$start_ts}' , '{$end_ts}');"); On psql, I can run a command such as select * from MakeSimpleReservation(2, 30, 1, '2010-04-12 11:00:00', '2010-04-12 14:00:00'); without any problems, but when I try to run it via PHP, I get this error message: ERROR: function makesimplereservation(integer, integer, integer, unknown, unknown) does not exist LINE 1: SELECT MakeSimpleReservation(2,3, 1, '2010-04-12 11:00:00' ... ^ HINT: No function matches the given name and argument types. Youmight need to add explicit type casts. For some reason, the last two function parameters are not recognized as strings. Is this a problem with PHP or with PostgreSQL?Can anyone give me advice how to fix this? Tuo
On Wed, Apr 7, 2010 at 10:11 AM, Tuo Pe <tuo_pe@yahoo.com> wrote: > Hi! > > I have written this function in PL/pgSQL, > CREATE OR REPLACE FUNCTION MakeSimpleReservation(integer, integer, integer, text, text) RETURNS boolean ... > > In my PHP script, I have this code: > $start_ts = '2010-04-12 11:00:00'; > $end_ts = '2010-04-12 14:00:00'; > > $update = pg_query($yhteys, "SELECT MakeSimpleReservation(2, 3, 1, '{$start_ts}' , '{$end_ts}');"); > > On psql, I can run a command such as > > select * from MakeSimpleReservation(2, 30, 1, '2010-04-12 11:00:00', '2010-04-12 14:00:00'); > > without any problems, but when I try to run it via PHP, I get this error message: > > ERROR: function makesimplereservation(integer, integer, integer, unknown, unknown) does not exist LINE 1: SELECT MakeSimpleReservation(2,3, 1, '2010-04-12 11:00:00' ... ^ HINT: No function matches the given name and argument types. Youmight need to add explicit type casts. > > For some reason, the last two function parameters are not recognized as strings. Is this a problem with PHP or with PostgreSQL?Can anyone give me advice how to fix this? > Show me the php code.
Tuo Pe <tuo_pe@yahoo.com> writes: > I have written this function in PL/pgSQL, > CREATE OR REPLACE FUNCTION MakeSimpleReservation(integer, integer, integer, text, text) RETURNS boolean ... > On psql, I can run a command such as > select * from MakeSimpleReservation(2, 30, 1, '2010-04-12 11:00:00', '2010-04-12 14:00:00'); > without any problems, but when I try to run it via PHP, I get this error message: > ERROR: function makesimplereservation(integer, integer, integer, unknown, unknown) does not exist LINE 1: SELECT MakeSimpleReservation(2,3, 1, '2010-04-12 11:00:00' ... ^ HINT: No function matches the given name and argument types. Youmight need to add explicit type casts. Hmm. That function should match that call. Are you sure php is connecting to the same database you created the function in? Or maybe you created the function in a schema that is not in the php client's search_path? regards, tom lane
> Hi! > > I have written this function in PL/pgSQL, > CREATE OR REPLACE FUNCTION MakeSimpleReservation(integer, integer, > integer, text, text) RETURNS boolean ... > > In my PHP script, I have this code: > $start_ts = '2010-04-12 11:00:00'; > $end_ts = '2010-04-12 14:00:00'; > > $update = pg_query($yhteys, "SELECT MakeSimpleReservation(2, 3, 1, > '{$start_ts}' , '{$end_ts}');"); > > On psql, I can run a command such as > > select * from MakeSimpleReservation(2, 30, 1, '2010-04-12 11:00:00', > '2010-04-12 14:00:00'); > > without any problems, but when I try to run it via PHP, I get this error > message: > > ERROR: function makesimplereservation(integer, integer, integer, unknown, > unknown) does not exist LINE 1: SELECT MakeSimpleReservation(2, 3, 1, > '2010-04-12 11:00:00' ... ^ HINT: No function matches the given name and > argument types. You might need to add explicit type casts. > > For some reason, the last two function parameters are not recognized as > strings. Is this a problem with PHP or with PostgreSQL? Can anyone give me > advice how to fix this? (a) Why are you passing timestamps as text? Try to use 'timestamp' instead of 'text' and then cast the values using ::timestamp (e.g. '2010-04-12 11:00:00'::timestamp). (b) If you really need to pass the values as text, you can cast using ::text (so '2010-04-12 11:00:00'::text will do the trick). (c) As the function returns boolean, you should use select makesimplereservation(....) AS 'result'; and not 'select * from ...' - this should be used with SRF (set returning functions) and I'm not sure if it will work with scalar values. Tomas
Op 7 apr 2010, om 18:11 heeft Tuo Pe het volgende geschreven: > Hi! > > I have written this function in PL/pgSQL, > CREATE OR REPLACE FUNCTION MakeSimpleReservation(integer, integer, > integer, text, text) RETURNS boolean ... text, text ? > In my PHP script, I have this code: > $start_ts = '2010-04-12 11:00:00'; > $end_ts = '2010-04-12 14:00:00'; Looks like timestamps to me, why do you use TEXT in your function? > $update = pg_query($yhteys, "SELECT MakeSimpleReservation(2, 3, 1, > '{$start_ts}' , '{$end_ts}');"); Why not pg_query_params? That's made to receive parameters: $update = pg_query_params($yhteys, "SELECT MakeSimpleReservation(2, 3, 1, $1, $2);", array($start_ts, $end_ts)); > > On psql, I can run a command such as > > select * from MakeSimpleReservation(2, 30, 1, '2010-04-12 11:00:00', > '2010-04-12 14:00:00'); > > without any problems, but when I try to run it via PHP, I get this > error message: > > ERROR: function makesimplereservation(integer, integer, integer, > unknown, unknown) does not exist LINE 1: SELECT > MakeSimpleReservation(2, 3, 1, '2010-04-12 11:00:00' ... ^ HINT: No > function matches the given name and argument types. You might need > to add explicit type casts. > > For some reason, the last two function parameters are not recognized > as strings. Is this a problem with PHP or with PostgreSQL? Can > anyone give me advice how to fix this? > > Tuo > > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general Frank Heikens frankheikens@mac.com
On Wed, Apr 7, 2010 at 10:11 AM, Tuo Pe <tuo_pe@yahoo.com> wrote: > Hi! > > I have written this function in PL/pgSQL, > CREATE OR REPLACE FUNCTION MakeSimpleReservation(integer, integer, integer, text, text) RETURNS boolean ... So, is this function created exactly like this? Is it possible you quoted the name MakeSimpleReservation to keep the capitalization? If so that would be the problem as it's getting lower case wrapped when called from php. Just a guess. Could you provide a complete test case to show it not working in php (i.e. a complete function, just strip out the guts and replace it with select 1 or something)
Hello! I want to thank everyone who replied to my question. I managed to solve my problem. I followed Tomas' suggestion and added '::text' to query string. Ultimately, however, the problem was that the function existedin another schema. (Thanks, Tom, for pointing me to this direction!) What I did was to add another pg_query, justbefore the "main" query: pg_query($yhteys, "SET search_path TO htyo;"); That did the trick! Again, thanks for saving me hours of frustration. Tuo