Обсуждение: 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

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

PostgreSQL Admin <postgres@productivitymedia.com> 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

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

Tom Lane wrote:
> PostgreSQL Admin <postgres@productivitymedia.com> 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

Thanks,

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

Big thanks,
J

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