Обсуждение: Problem either with PostgreSQL or with PHP

Поиск
Список
Период
Сортировка

Problem either with PostgreSQL or with PHP

От
Tuo Pe
Дата:
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

Re: Problem either with PostgreSQL or with PHP

От
Scott Marlowe
Дата:
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.

Re: Problem either with PostgreSQL or with PHP

От
Tom Lane
Дата:
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

Re: Problem either with PostgreSQL or with PHP

От
tv@fuzzy.cz
Дата:
> 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

Re: Problem either with PostgreSQL or with PHP

От
Frank Heikens
Дата:
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

Re: Problem either with PostgreSQL or with PHP

От
Scott Marlowe
Дата:
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)

Re: Problem either with PostgreSQL or with PHP (SOLVED!)

От
Tuo Pe
Дата:
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