pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Adam Witney <awitney@sgul.ac.uk>
To: Ganesh Laxmanmurthy <lganimys@yahoo.co.in>
Cc: pgsql-php@postgresql.org
Subject: Re: PLPGSQL - PHP data querying
Date: Wed, 06 Dec 2006 11:02:29 +0000
Message-ID: <4576A345.6040109@sgul.ac.uk> (raw)
In-Reply-To: <938994.73369.qm@web8504.mail.in.yahoo.com>
References: <938994.73369.qm@web8504.mail.in.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.




view thread (3+ messages)  latest in thread

Message-ID: <4576A345.6040109@sgul.ac.uk>
Permalink:  ../4576A345.6040109@sgul.ac.uk/
Also on:    postgresql.org/message-id/4576A345.6040109@sgul.ac.uk

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-php@postgresql.org
  Cc: awitney@sgul.ac.uk, lganimys@yahoo.co.in
  Subject: Re: PLPGSQL - PHP data querying
  In-Reply-To: <4576A345.6040109@sgul.ac.uk>
  References: <938994.73369.qm@web8504.mail.in.yahoo.com> <4576A345.6040109@sgul.ac.uk>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox