PostgreSQL PHP: calling stored procedure demo

Php postgresql вызов хранимой процедуры

Профиль
Группа: Участник
Сообщений: 90
Регистрация: 28.7.2006

Здравствуйте.
Только изучаю хранимые процедуры и функции, отсюда этот вопрос.
Написал функцию на plpgsql
Выполнил соответствующий запрос СREATE OR REPLACE FUNCTION. в результате чего в дереве объектов БД появилась моя функция.

Нашел кучу примеров, как вызывать из Java, но..разве вызов нельзя осуществить из SQL-редактора pgAdmin-а?

PREPARE STATEMENT и EXECUTE, насколько я понял, предназначены для погдотовки запросов и их выполнения..а как же вызвать мою функцию?

Елки, надо было с ветке postgreSQL разместиться

Это сообщение отредактировал(а) Alexander06 — 8.6.2009, 00:05

Профиль
Группа: Завсегдатай
Сообщений: 3470
Регистрация: 12.9.2008

M Zloxa

Перенесено из составление SQL запросов

Профиль
Группа: Участник
Сообщений: 16
Регистрация: 13.4.2009

как такового понятия «процедура» в постгресе нет, все функции обязаны иметь возвращаемое значение (исключение разве что для void)

советую изучать не методом тыка, а предварительно прочитав мануал — чрезвычайно помогает от подобных вопросов

Это сообщение отредактировал(а) msr — 8.6.2009, 01:44

Профиль
Группа: Участник
Сообщений: 90
Регистрация: 28.7.2006

Согласен, с них и начал, взял мануал постгресса и книгу по PL/SQL ( вроде как plpgsql очень похож на оракловский язык), но мои навыки не успевают за производственной задачей.

[ Время генерации скрипта: 0.0994 ] [ Использовано запросов: 21 ] [ GZIP включён ]

Источник

PostgreSQL PHP: Calling Stored Procedures

Summary: in this tutorial, you will learn how to call stored procedures in PostgreSQL in PHP using PDO.

Calling a stored procedure that returns one value

Let’s create a simple stored procedure named add() that returns the product of two integers using plpgsql.

CREATE OR REPLACE FUNCTION add( a INTEGER, b INTEGER) RETURNS integer AS $$ BEGIN return a + b; END; $$ LANGUAGE 'plpgsql';Code language: SQL (Structured Query Language) (sql)

To call a stored procedure that returns one value, you use these steps:

1. Connect to the PostgreSQL database server by creating a new instance of the PDO class.
2. Prepare the statement that calls the stored procedure for execution using the prepare() method of the PDO object. The prepare() method returns a PDOStatement object.
3. Optionally pass values to the statement using the bindValue() method.
4. Execute the statement using the execute() method of the PDOStatement object. You can pass the values to the statement when calling the execute() method as well.
5. Get the value using the fetchColumn() method that returns a single column of the next row in the result set.

The following add() method demonstrates how to call the add() stored procedure in PostgreSQL database.

 /** * Call a simple stored procedure * @param int $a * @param int $b * @return int */ public function add($a, $b) < $stmt = $this->pdo->prepare('SELECT * FROM add(:a,:b)'); $stmt->setFetchMode(\PDO::FETCH_ASSOC); $stmt->execute([ ':a' => $a, ':b' => $b ]); return $stmt->fetchColumn(0); >Code language: PHP (php)

To test the add() method, you use the following code in the index.php file:

 require 'vendor/autoload.php'; use PostgreSQLTutorial\Connection as Connection; use PostgreSQLTutorial\StoreProc as StoreProc; try < // connect to the PostgreSQL database $pdo = Connection::get()->connect(); // $storeProc = new StoreProc($pdo); $result = $storeProc->add(20, 30); echo $result; > catch (\PDOException $e) < echo $e->getMessage(); >Code language: PHP (php)

Calling a stored procedure that returns a result set

We will use the accounts , plans , and account_plans tables for the sake of demonstration. The following get_accounts() stored procedure returns a result set that contains a complete data of accounts.

CREATE OR REPLACE FUNCTION get_accounts() RETURNS TABLE(id integer, first_name character varying, last_name character varying, plan character varying, effective_date date) AS $$ BEGIN RETURN QUERY SELECT a.id,a.first_name,a.last_name, p.plan, ap.effective_date FROM accounts a INNER JOIN account_plans ap on a.id = account_id INNER JOIN plans p on p.id = plan_id ORDER BY a.id, ap.effective_date; END; $$ LANGUAGE plpgsql;Code language: SQL (Structured Query Language) (sql)

The steps of calling a stored procedure that returns a result set are the same as the steps of querying data.

The following getAccounts() method demonstrates how to call the get_accounts() stored procedure in PHP.

 /** * Call a stored procedure that returns a result set * @return array */ function getAccounts() < $stmt = $this->pdo->query('SELECT * FROM get_accounts()'); $accounts = []; while ($row = $stmt->fetch()) < $accounts[] = [ 'id' => $row['id'], 'first_name' => $row['first_name'], 'last_name' => $row['last_name'], 'plan' => $row['plan'], 'effective_date' => $row['effective_date'] ]; > return $accounts; >Code language: PHP (php)

To test the getAccounts() method, you use the following code in the account.php file.

use PostgreSQLTutorial\Connection as Connection; use PostgreSQLTutorial\StoreProc as StoreProc; try < // connect to the PostgreSQL database $pdo = Connection::get()->connect(); // $storeProc = new StoreProc($pdo); $accounts = $storeProc->getAccounts(); > catch (\PDOException $e) < echo $e->getMessage(); > ?>      
Account List
   ID First Name Last Name Plan Effective Date   Code language: SQL (Structured Query Language) (sql)

In this tutorial, we have shown you how to call stored procedure from PostgreSQL using PHP PDO.

Источник

How to call PostgreSQL stored procedures from a PHP application

SUMMARY: This tutorial provides instructions and an example for calling a PostgreSQL stored procedure from a PHP application.

1. Creating the PostgreSQL stored procedure

2. Creating the example program in PHP

3. Running the example program

Prerequisites

For this demonstration we will be using the following software versions:

Example of using a stored procedure in a PHP application

1. Creating the PostgreSQL stored procedure

The example program will call the following stored procedure, which adds two complex numbers and returns the result in INOUT parameters. Let’s start by creating a stored procedure:

CREATE OR REPLACE PROCEDURE add_complex(IN real_1 INTEGER, IN imaginary_1 INTEGER, IN real_2 INTEGER, IN imaginary_2 INTEGER, INOUT real_res INTEGER, INOUT imaginary_res INTEGER) AS $$ BEGIN real_res := real_1 + real_2; imaginary_res := imaginary_1 + imaginary_2; END; $$ LANGUAGE plpgsql;

2. Creating the example program in PHP

Now, let’s call this stored procedure using PHP.

[abbas@localhost html]$ pwd /var/www/html

Create the following file sp.php at the above location:

sp.php

($real_1+i$imaginary_1)+($real_2+i$imaginary_2)=($res_real+i$res_imaginary)
"; pg_close($dbconn); ?> [abbas@localhost html]$ pwd /var/www/html

Update the file index.html at the above location with the content as shown below:

index.html

    
Execute SP to add two complex numbers
  

3. Running the example program

Now open the file in browser 127.0.0.1 and click on “Execute SP to add two complex numbers”:

Relevant Blogs

EDB Tutorial: How to Configure Databases for EDB JDBC SSL Factory Classes

The fact is, SSL configuration required by EDB JDBC SSL factory classes is considered confusing and challenging by many EDB customers. This stems from the concepts behind the SSL handshake.

EDB Tutorial: How To Run a Complex Postgres Benchmark Easily — Master TPC-C in 3 Short Steps

Benchmarking is one of the best ways to ensure the ongoing performance of your Postgres database, providing vital insights that can prove incredibly useful when growing and scaling. Luckily there.

An Overview of PostgreSQL Indexes

SUMMARY: This article describes indexes in PostgreSQL and how they can help retrieve data faster. It covers the types of indexes.

More Blogs

How to use block structure to write anonymous blocks and divide larger blocks into logical subblocks

SUMMARY: This article discusses block structure in PostgreSQL, how to write an anonymous block, and how to divide a larger block into logical subblocks. 1. Block structure .

Postgres AT TIME ZONE Explained

I saw AT TIME ZONE used in a query, and found it confusing. I read the Postgres documentation and was still confused, so I played with some queries and finally figured it.

PostgreSQL and Golang Tutorial

SUMMARY: This article covers how to use PostgreSQL together with the programming language Golang (Go). After showing how to get started with Go’s Object Relational Mapper, it.

Источник

Как вызвать с передачей параметра функции хранимых процедур в Postgres SQL

Я борюсь с как вызвать функцию хранимой процедуры PostgreSQL с передачей параметра.Я не знаю, и я ошибся с передачей параметра вызова функции. Пожалуйста, посоветуйте мне.

Это мой php файл calling.php

 include('connection.php'); echo $hud='15'; echo $phc='80001'; echo $Firstdate='2015-08-01'; echo $Seconddate='2015-10-01'; echo $Todate='2015-10-31'; $dvn_sql =

И это мои функции хранимых процедур

 CREATE OR REPLACE FUNCTION prisonparam(dvn_cd text ,phc_cd text ,Firstdate Date ,Seconddate Date ,Todate Date) RETURNS table (round text,sc bigint,scupto bigint) AS $$ WITH a AS ( SELECT round AS round ,Sum(ben_sc) AS SC FROM prison_issue WHERE ( DATE BETWEEN Firstdate AND Todate ) AND dvn_cd = dvn_cd AND phc_cd = phc_cd GROUP BY round ORDER BY round ) ,b AS ( SELECT round AS round_up ,Sum(ben_sc) AS SC_up FROM prison_issue WHERE ( DATE BETWEEN Seconddate AND Todate ) AND dvn_cd = dvn_cd AND phc_cd = phc_cd GROUP BY round ORDER BY round ) SELECT b.round_up AS round ,coalesce(a.sc, 0) AS SC ,coalesce(b.sc_up, 0) AS SCUPTO FROM a RIGHT JOIN b ON a.round = b.round_up $$ LANGUAGE sql; 

Warning: pg_query(): Query failed: ERROR: function prisonparam(integer, integer, integer, integer, integer) does not exist LINE 1: select * from prisonparam(15,80001, 2015-08-01,2015-10. ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. in C:\wamp\www\mhp\test.php on line 12 

Решение

Попробуйте заменить запрос

select * from prisonparam($hud text ,$phc text ,$Firstdate Date ,$Seconddate Date ,$Todate Date); 

select * from prisonparam('$hud','$phc','$Firstdate','$Seconddate' ,'$Todate'); 

$dvn_sql ="select * from prisonparam('".$hud."','".$phc."','".$Firstdate."','".$Seconddate."','".$Todate."')"; 

Другие решения

Источник