Re: PLPGSQL - PHP data querying

From: Adam Witney <awitney(at)sgul(dot)ac(dot)uk>
To: Ganesh Laxmanmurthy <lganimys(at)yahoo(dot)co(dot)in>
Cc: pgsql-php(at)postgresql(dot)org
Subject: Re: PLPGSQL - PHP data querying
Date: 2006-12-06 11:02:29
Message-ID: 4576A345.6040109@sgul.ac.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-php

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.

In response to

Browse pgsql-php by date

  From Date Subject
Next Message Andy Shellam (Mailing Lists) 2006-12-06 13:17:43 Re: PLPGSQL - PHP data querying
Previous Message Ganesh Laxmanmurthy 2006-12-06 10:20:56 PLPGSQL - PHP data querying