就用途而言，准备好的语句与SQL或PL/pgSQL函数有什么区别? [英] What is the difference between prepared statements and SQL or PL/pgSQL functions, in terms of their purpose?

问题描述

在PostgreSQL中，就其目的，优点和缺点而言，预备语句与SQL或PL/pgSQL函数之间有什么区别?我们什么时候使用哪个?

In PostgreSQL, what is the difference between a prepared statement and a SQL or PL/pgSQL function, in terms of their purposes, advantages and disadvantages? When shall we use which?

在这个非常简单的示例中，它们的工作原理相同吗，对吗?

In this very simple example, do they work the same, correct?

CREATE TABLE foo (id INT, name VARCHAR(80)); 

CREATE FUNCTION myfunc1(INT, VARCHAR(80)) RETURNS void AS ' 
INSERT INTO foo VALUES ($1, $2);
' LANGUAGE SQL; 

SELECT myfunc1(3, 'ben');

CREATE FUNCTION myfunc2(INT, VARCHAR(80)) RETURNS void AS ' 
BEGIN
INSERT INTO foo VALUES ($1, $2);
END' LANGUAGE plpgsql; 

SELECT myfunc2(3, 'ben');

PREPARE fooplan (INT, VARCHAR(80)) AS
    INSERT INTO foo VALUES($1, $2);
PREPARE

EXECUTE fooplan(3, 'ben');

推荐答案

所有三个工作相同"，因为它们执行简单的SQL语句:

All three "work the same" in that they execute the simple SQL statement:

INSERT INTO foo VALUES (3, 'ben');

准备好的语句仅对单准备好的SQL语句(如其名称所示)有用.而且只有DML命令. 手册:

The prepared statement is only good for a single prepared SQL statement (as the name suggests). And only DML commands. The manual:

任何SELECT，INSERT，UPDATE，DELETE或VALUES语句.

函数可以包含任意数量的语句. DML 和 DDL.只有SQL for SQL函数.加上PL/pgSQL中的一些非SQL程序元素.

Functions can contain any number of statements. DML and DDL. Only SQL for SQL functions. Plus some non-SQL procedural elements in PL/pgSQL.

准备好的语句仅在同一会话中可见，并且在会话结束时消失，而这些函数将保留并对所有人可见-仍然仅对具有EXECUTE特权的用户可用.

The prepared statement is only visible inside the same session and gone at the end of the session, while the functions persist and are visible to all - still only usable for those with the EXECUTE privilege.

准备好的语句的负担最少. (相差不大.)

The prepared statement is encumbered with the least overhead. (Not much difference.)

SQL函数是不能保存查询计划的三个函数中的唯一一个(单独). 在手册在这里.

The SQL function is the only one of the three that cannot save the query plan (by itself). Read details about plan caching in PL/pgSQL functions in the manual here.

SQL函数也是唯一可以 内联的函数. > (在更大的查询中使用). (不过，不是INSERT.)

The SQL function is also the only one that could be inlined when used within a bigger query. (Not with an INSERT, though.)

SQL和PL/pgSQL函数之间的差异的相当全面的列表:

A rather comprehensive list of differences between SQL and PL/pgSQL functions:

• 语言sql和PostgreSQL函数中的语言plpgsql

从Postgres 11开始，还有SQL过程:

Starting with Postgres 11 there are also SQL procedures:

• 何时使用存储过程/用户定义的功能?

这篇关于就用途而言，准备好的语句与SQL或PL/pgSQL函数有什么区别?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！