PL/pgSQL and PHP 5

I'm having this problem inserting data from my form using PL/pgSQL.
Here is the simplified version of my table and function (this example
does not work, also ):

CREATE TABLE theirry.sample (
staff_id serial PRIMARY KEY NOT NULL,
firstname varchar(100),
lastname varchar(150),
username varchar(35),
identifier varchar(40),
address2 varchar(180),
activated boolean,
activated_keys varchar(32)
);

CREATE OR REPLACE FUNCTION insert_staff_b
(insert_firstname varchar)
RETURNS VOID AS
$$
DECLARE
BEGIN
INSERT INTO theirry.sample
(firstname)
VALUES
(insert_firstname);
RETURN;
END;
$$
LANGUAGE plpgsql;


I have a form with a value firstname then call the query in php

select insert_staff_b('$_POST['firstname']::varchar)


Still I get this error:
Warning: pg_query(): Query failed: ERROR: function
insert_staff_b(character varying) does not exist HINT: No function
matches the given name and argument types. You may need to add explicit
type casts.

Suggestions or maybe a place to read up on this problem.

Thanks in advance,
J

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Re: PL/pgSQL and PHP 5

On Aug 9, 2006, at 10:36 PM, PostgreSQL Admin wrote:

> select insert_staff_b('$_POST['firstname']::varchar)
>
>
> Still I get this error:
> Warning: pg_query(): Query failed: ERROR: function insert_staff_b
> (character varying) does not exist HINT: No function matches the
> given name and argument types. You may need to add explicit type
> casts.


Your select statement above has unbalanced single quotes. Assuming
this is not really the issue, I would check the search_path and look
at the function in psql or some admin tool to make sure the function
name does not have different capitalization.




John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] PL/pgSQL and PHP 5

PostgreSQL Admin writes:
> CREATE OR REPLACE FUNCTION insert_staff_b
> (insert_firstname varchar)
> RETURNS VOID AS
> ...
> Still I get this error:
> Warning: pg_query(): Query failed: ERROR: function
> insert_staff_b(character varying) does not exist

Sure looks like it oughta work. One possibility is that you created the
function in a schema that isn't part of the application's search path.
Other than that, look for *really* silly errors, like not creating the
function in the same database the application is connected to ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Re: PL/pgSQL and PHP 5

Thanks for the catch. I've tried:

$connection->execute("SELECT
insert_staff_b('$staff[insert_firstname]'::varchar)");
$connection->execute("SELECT
insert_staff_b('".$staff['insert_firstname']."'::varchar)");

None work... I'm scratching my head on this one.

Thanks,
J

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Re: [SQL] PL/pgSQL and PHP 5

Tom Lane wrote:
> PostgreSQL Admin writes:
>
>> CREATE OR REPLACE FUNCTION insert_staff_b
>> (insert_firstname varchar)
>> RETURNS VOID AS
>> ...
>> Still I get this error:
>> Warning: pg_query(): Query failed: ERROR: function
>> insert_staff_b(character varying) does not exist
>>
>
> Sure looks like it oughta work. One possibility is that you created the
> function in a schema that isn't part of the application's search path.
> Other than that, look for *really* silly errors, like not creating the
> function in the same database the application is connected to ...
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
>
I never thought of that. I look into it.

Thanks,
J

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Re: [SQL] PL/pgSQL and PHP 5 - thanks

Thanks,

The search path was the problem. Sometimes it's the simple things.

Big thanks,
J

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Re: PL/pgSQL and PHP 5

Glad you found the problem.

On Aug 9, 2006, at 11:42 PM, PostgreSQL Admin wrote:

> $connection->execute("SELECT insert_staff_b('$staff
> [insert_firstname]'::varchar)");
> $connection->execute("SELECT insert_staff_b('".$staff
> ['insert_firstname']."'::varchar)");

If you are creating SQL functions you want to call from PHP, you
might be interested in this simple class:

http://pgedit.com/resource/php/pgfuncall


Then instead of all the quoting issue you have above, you could
simply call your SQL function like a normal PHP method call:

$connection->insert_staff_b($staff['insert_firstname']);




John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster