PLPGSQL - PHP data querying
PLPGSQL - PHP data querying
От:
Ganesh Laxmanmurthy <lganimys@yahoo.co.in>
Дата:
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/
Re: PLPGSQL - PHP data querying
От:
Adam Witney <awitney@sgul.ac.uk>
Дата:
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']."
";
}
}
}
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
Re: PLPGSQL - PHP data querying
От:
"Andy Shellam (Mailing Lists)" <andy.shellam-lists@mailnetwork.co.uk>
Дата:
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