Thread: PLPGSQL - PHP data querying

Dear All:

Regarding PHP & PLPGSQL Programming. I am new to
PLPGSQL.

I wish someone suggest me the solution for the below
requirement even though it is very simple as I am new
to the subject and could not find an answer which I
can understand.

I have a table by name 'users' with several columns.

I want to query "select * from users' from PHP to
Postgresql through PLPGSQL.

Therefore I need

1. The practical syntax for the PLPGSQL function

2. The practical syntax for calling and displaying of
the returned recordset from PHP.

With the above I would be able to develop futher
programs.

I would be thankful for the same.

Thanks in advance

Regards

Ganesh



__________________________________________________________
Yahoo! India Answers: Share what you know. Learn something new
http://in.answers.yahoo.com/

Ganesh Laxmanmurthy wrote:
> Dear All:
>
> Regarding PHP & PLPGSQL Programming. I am new to
> PLPGSQL.
>
> I wish someone suggest me the solution for the below
> requirement even though it is very simple as I am new
> to the subject and could not find an answer which I
> can understand.
>
> I have a table by name 'users' with several columns.
>
> I want to query "select * from users' from PHP to
> Postgresql through PLPGSQL.
>
> Therefore I need
>
> 1. The practical syntax for the PLPGSQL function
>
> 2. The practical syntax for calling and displaying of
> the returned recordset from PHP.
>
> With the above I would be able to develop futher
> programs.
>
> I would be thankful for the same.

here is some untested code, but will give one way of doing it (note
there is also no great error checking in it):


CREATE TABLE users (id int, name text);

CREATE OR REPLACE FUNCTION my_function() RETURNS SETOF record AS '
 DECLARE
    _record record;

 BEGIN

      FOR _record IN SELECT * FROM users
       LOOP
         RETURN NEXT _record;
       END LOOP;

       RETURN;
 END;

' LANGUAGE 'plpgsql';


############################# PHP code #############################

$connstr = "dbname=".$dbname." host=".$host." port=".$port."
user=".$user." password=".$passwd;

$dbh = pg_connect($connstr);


$sql = "SELECT * FROM my_function() AS (id int, name text);";

if($stat = pg_exec($dbh, $sql);)
   {
    if($rows = pg_numrows($stat))
       {
        for($i=0; $i < $rows; $i++)
            {
             $data = pg_fetch_array($stat, $i);

             echo $data['id']." -- ".$data['name']."<br>";
            }
       }
   }

--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

Why do you specifically want to do this through PlPgsql?

You can just as easily do a query directly from PHP to get a recordset,
and it gives less server overhead.

Andy.

Ganesh Laxmanmurthy wrote:
> Dear All:
>
> Regarding PHP & PLPGSQL Programming. I am new to
> PLPGSQL.
>
> I wish someone suggest me the solution for the below
> requirement even though it is very simple as I am new
> to the subject and could not find an answer which I
> can understand.
>
> I have a table by name 'users' with several columns.
>
> I want to query "select * from users' from PHP to
> Postgresql through PLPGSQL.
>
> Therefore I need
>
> 1. The practical syntax for the PLPGSQL function
>
> 2. The practical syntax for calling and displaying of
> the returned recordset from PHP.
>
> With the above I would be able to develop futher
> programs.
>
> I would be thankful for the same.
>
> Thanks in advance
>
> Regards
>
> Ganesh
>
>
>
> __________________________________________________________
> Yahoo! India Answers: Share what you know. Learn something new
> http://in.answers.yahoo.com/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
> !DSPAM:37,45769b3640411164512130!
>
>
>


--
Andy Shellam
NetServe Support Team

the Mail Network
"an alternative in a standardised world"

p: +44 (0) 121 288 0832/0839
m: +44 (0) 7818 000834