创建过程以在PostgreSQL中执行查询 [英] Create procedure to execute query in PostgreSQL

问题描述

也许这不是新情况，但我对此很感兴趣.这是我用来运行查询的过程，它通常在MySQL中运行，但在PostgreSQL中不运行，我也不知道该怎么做.该过程(在MySQL中)如下所示:

Maybe it's not new case, but I'm stack about it. This is the procedure that I use to run query, it run normally in MySQL, but not in PostgreSQL and I don't know how to do that. The procedure(in MySQL) looks like :

CREATE PROCEDURE runstatement(IN statement TEXT)
BEGIN 
 set @s = statement; 
 IF LENGTH(@s) <> 0 THEN PREPARE stmt FROM @s; 
 EXECUTE stmt; 
 DEALLOCATE PREPARE stmt; 
 END IF; 
END

问题:

1. 如何将其转换为PostgreSQL版本?

2. 当我在另一个过程中需要它时如何调用该过程(运行声明)?在MySQL中，我称为CALL runstatement(param).

感谢您的每次回复.我是数据库编程方面的新手，尤其是PostgreSQL.

Thanks for each reply. I'm new in database programming especially PostgreSQL.

推荐答案

此技术称为动态SQL . PLpgSQL在这种情况下具有EXECUTE语句. 因为EXECUTE是一行命令，所以可能无法为此专门编写特殊功能.

This technique is named dynamic SQL. PLpgSQL has EXECUTE statement for this case. Probably is useless to write special function just for this, because EXECUTE is one line command.

CREATE OR REPLACE FUNCTION runstatement(statement TEXT)
RETURNS void AS $$
BEGIN
  IF statement <> '' THEN
    EXECUTE statement;
  END IF;
END;
$$ LANGUAGE plpgsql;

测试空字符串可能是错误的设计.这种情况不应该如此. Assert在那里更好.

Probably test on empty string is bad design. This case should not to be. Assert is better there.

此函数可以称为:

SELECT runstatement(''); -- outside plpgsql

或

PERFORM runstatement('') -- inside plpgsql

请参见相关部分在文档中.

这篇关于创建过程以在PostgreSQL中执行查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！