Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2004
    Posts
    110

    Unanswered: pl/pgsql function not returning result into php

    As im exploring the world of pl/pgsql i've tried to incorporate a function with php.

    As a test i used:

    The table:
    CREATE TABLE public.testing
    (
    col1 int4,
    col2 int4,
    col3 int4);

    INSERT INTO testing VALUES (1,2,3);

    The pl/pgsql function:
    CREATE FUNCTION test_function(integer) RETURNS INTEGER AS'
    DECLARE
    cijfer integer;
    rec RECORD;
    BEGIN
    cijfer := $1;
    SELECT INTO rec col1 FROM testing WHERE col3=cijfer;
    RETURN rec.col1;
    END;

    ' LANGUAGE 'plpgsql';

    And the php script:
    ## my_integ is an integer that comes from a previous page.
    ## and is converted to $my_integ as a variable.

    $query = "select test_function($my_integ);
    if (!$query) {
    echo "An error occured.\n";
    exit;
    }
    $result = @pg_fetch_result($query);
    var_dump($result);

    The problem is the following. When i execute the following select, in pgAdmin:
    select test_function(3);
    i get the desired result.
    facturen=# select test_function(3);

    NOTICE: Existing id found
    test_function
    ---------------
    1
    (1 row)

    facturen=#

    But when i do the same select through php i get a null result:
    -----------
    3
    connection to database: succesfull

    NULL
    ------------

    the "3" is an echo "$my_integ" to check if the integer is actually used.
    the connection to database: succesfull is a check to see if im connecting to the db.

    and the NULL is the result the query apparently returns from doing the select on the function.

    Obviously im missing something. So how do i get a result back from a function into php?

    -Ed
    a noobie postgresql user.

    *edit* improved syntax on the function.. still the same thing happens.
    Last edited by Edje; 04-15-04 at 17:59.

  2. #2
    Join Date
    Mar 2004
    Posts
    110
    A good night sleep did it for me.

    When u do a normale select on a table and what the results back in php, you usually use the "for($i=0 etc construnction.

    I thought i'd be smart and do a var_dump but somehow that doesnt work for me.

    Till i realised that the result that comes back from a function is always called "function_name" and below the result. So when i changed the php code into:

    for($i=0;
    $row = @pg_fetch_array($result,$i); $i++)
    {
    echo "Col1: $row[test_function]<BR>";
    }

    ... i got the result back in php.

    -Ed

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •