PHP PostgreSQL Example – Pass Array To Postgres Function

Today I will show you how to pass array to Postgres Function using php. First we will create a table named country that has two columns id and name. You can run the below query to create the table.

    CREATE TABLE country
(
  id serial NOT NULL,
  name character varying(100),
  CONSTRAINT country_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE country
  OWNER TO postgres;

Next we will create a function in PostgreSQL that will accept array as country names and loop through the array to insert values into the table country.

    CREATE OR REPLACE FUNCTION country_insert(_name character varying[])
  RETURNS void AS
$BODY$
Declare i character varying;
BEGIN


FOREACH i IN ARRAY _name
LOOP 

  INSERT INTO country(name) VALUES(i);
  
END LOOP;



exception when others then 
    raise exception '% %', SQLERRM, SQLSTATE;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION country_insert(character varying[])
  OWNER TO postgres;

Above query will accept an array of type character varying. We will then loop through the array using foreach loop and insert the values into country table. Next we will create a html file that has a form which will accept an array of country names.

<!DOCTYPE html>

 

<html>

<head>

<title>Sample</title>

<meta charset="UTF-8">

<meta name="viewport" content="width=device-width, initial-scale=1.0">

</head>

<body>

<form action="submit.php" method="post">

<table border="0">

<thead>

<tr>

<th></th>

</tr>

</thead>

<tbody>

<tr>

<td><input type="text" name="country[]" value="" /></td>

</tr>

<tr>

<td><input type="text" name="country[]" value="" /></td>

</tr>

<tr>

<td><input type="text" name="country[]" value="" /></td>

</tr>

<tr>

<td><input type="text" name="country[]" value="" /></td>

</tr>

<tr>

<td><input type="text" name="country[]" value="" /></td>

</tr>

<tr>

<td><input type="submit" name="submit" value="Save" /></td>

</tr>

</tbody>

</table>

</form>

</body>

</html>

The form will submit its values to the file “submit.php” which has the below code in it.

    
    
<?php

 

$data=to_pg_array(array_values(array_filter($_POST['country'])));

$dbconn = pg_connect("host=localhost port=5432 dbname=test user=postgres password=postgres");

 

pg_prepare($dbconn, "my_query", 'select * from country_insert($1)');

pg_execute($dbconn, "my_query", array($data));

 

header("Location:newhtml.html");

 

 

function to_pg_array($set) {

settype($set, 'array');

$result = array();

foreach ($set as $t) {

if (is_array($t)) {

$result[] = to_pg_array($t);

} else {

$t = str_replace('"', '\\"', $t);

if (! is_numeric($t))

$t = '"' . $t . '"';

$result[] = $t;

}

}

return '{' . implode(",", $result) . '}';

}

?>

We have used array_filter () function to remove any empty values in the submitted array. Function array_values () will re-index the new array whose empty values are removed. The function to_pg_array() will convert the php array into PostgreSQL array.

Posted in PostgreSQL

•

29 Mar 2024

PHP PostgreSQL Example – Pass Array To Postgres Function

Categories

Archives