如何在plpgsql中使用记录类型变量？ [英] How to use a record type variable in plpgsql?

问题描述

如何将存储在记录类型变量中的查询结果用于同一个存储函数中的另一个查询？

 <$ c $使用Postgres 9.4.4。
 
 
 
 c> create table test（id int，tags text []）; 
插入测试值（1，'{a，b，c}'），
（2，'{c，d，e}'）; 
  

我写了一个如下所示的函数（简体）：

 创建或替换函数func（_tbl regclass）
 RETURNS TABLE（t TEXT []，e TEXT []）
 LANGUAGE plpgsql AS $$ 
 DECLARE 
t RECORD; 
 c INT; 
 BEGIN 
 EXECUTE格式（'SELECT id，tags FROM％s'，_tbl）INTO t; 
 SELECT count（*）FROM t INTO c; 
 RAISE NOTICE'％results'，c; 
 SELECT * FROM t; 
 END 
 $$; 
  

...但不起作用：

  select func（'test'）; 
  

 错误：42P01：关系t不存在
 LINE 1：SELECT count（*）FROM t 
 $ 
 QUERY：SELECT count（*）FROM t 
 CONTEXT：PL / pgSQL函数func regclass）第7行在SQL语句中
 LOCATION：parserOpenTable，parse_relation.c：986 
  

解决方案

核心误解：记录变量包含一行是NULL），而不是表（一个众所周知的类型的0-n行）。 Postgres或PL / pgSQL中没有表变量。根据任务的不同，有多种选择：

• PostgreSQL表变量


• 
  

因此，您不能将 多个 行分配给记录类型变量。在这个声明中：

pre $ EXECUTE格式（'SELECT id，tags FROM％s'，_tbl）INTO t;


... Postgres只分配第一行并丢弃其余的。由于第一个在你的查询中没有很好的定义，你最终会得到一个任意选择。显然是由于前面提到的误解。

记录变量也不能用于替换表格SQL查询。这是导致错误的主要原因：

关系t不存在

现在应该清楚， count（*）开始时没有任何意义，因为 t 只是一个单独的记录/行 - 除了无论如何也是不可能的。

最后（即使其余的工作）您的返回类型看起来不正确： （t TEXT []，e TEXT []） 。既然你选择了 id，tags 到 t ，你会想返回类似于（您可以尝试使用这样的工作方式： / b>



 创建或替换函数func（_tbl regclass）
 RETURNS TABLE（id int，e text []）AS 
 $ func $ 
 DECLARE 
 _ct int; 
 BEGIN 
 EXECUTE格式（
'CREATE TEMP TABLE tmp ON COMMIT DROP AS 
 SELECT id，tags FROM％s'
，_tbl）; 
 
 GET DIAGNOSTICS _ct = ROW_COUNT; - 比另一个计数便宜（*）
 
  -  ANALYZE tmp; - 如果您要运行多个查询
 
 RAISE通知'％results'，_ct; 
 
 RETURN QUERY TABLE tmp; 
 END 
 $ func $ LANGUAGE plpgsql; 
  

致电（注意语法！）：

  SELECT * FROM func（'test'）; 
  

相关：





• > Postgres从动态sql字符串创建本地临时表（在提交时删除）
  

只需证明概念。在选择整个表格时，您只需使用底层表格。实际上，在查询中会有一些 WHERE 子句...

注意潜伏类型不匹配， count（）返回 bigint ，您无法将它分配给整数变量。需要一个强制转换： count（*）:: int 。

但是我完全替换了它，它更便宜在 EXECUTE 之后运行：
$ b

  GET DIAGNOSTICS _ct = ROW_COUNT; 
  

手册中的详细信息。

为什么 ANALYZE ？

• 常规VACUUM ANALYZE仍然在9.1下推荐？

---

Aside ：普通SQL中的CTE通常可以完成这项工作：

$ b $从plpgsql中的FOR循环切换到基于集合的SQL命令b

How can I use query result stored into a record type variable for another query within the same stored function? I use Postgres 9.4.4.

With a table like this:

create table test (id int, tags text[]);
insert into test values (1,'{a,b,c}'),
                        (2,'{c,d,e}');

I wrote a function (simplified) like below:

CREATE OR REPLACE FUNCTION func(_tbl regclass)
RETURNS TABLE (t TEXT[], e TEXT[])
LANGUAGE plpgsql AS $$
DECLARE
  t RECORD;
  c INT;
BEGIN
  EXECUTE format('SELECT id, tags FROM %s', _tbl) INTO t;
  SELECT count(*) FROM t INTO c;
  RAISE NOTICE '% results', c;
  SELECT * FROM t;
END
$$;

... but didn't work:

select func('test');

ERROR:  42P01: relation "t" does not exist
LINE 1: SELECT count(*) FROM t
                             ^
QUERY:  SELECT count(*) FROM t
CONTEXT:  PL/pgSQL function func(regclass) line 7 at SQL statement
LOCATION:  parserOpenTable, parse_relation.c:986

解决方案

The core misunderstanding: a record variable holds a single row (or is NULL), not a table (0-n rows of a well-known type). There are no "table variables" in Postgres or PL/pgSQL. Depending on the task, there are various alternatives:

• PostgreSQL table variable
• SELECT multiple rows and columns into a record variable

Accordingly, you cannot assign multiple rows to a record type variable. In this statement:

EXECUTE format('SELECT id, tags FROM %s', _tbl) INTO t;

... Postgres assigns only the first row and discards the rest. Since "the first" is not well defined in your query, you end up with an arbitrary pick. Obviously due to the misunderstanding mentioned at the outset.

A record variable also cannot be used in place of tables in SQL queries. That's the primary cause of the error you get:

relation "t" does not exist

It should be clear by now, that count(*) wouldn't make any sense to begin with, since t is just a single record / row - besides being impossible anyway.

Finally (even if the rest would work), your return type seems wrong: (t TEXT[], e TEXT[]). Since you select id, tags into t, you'd want to return something like (id int, e TEXT[]).

What you are trying to do would work like this:

CREATE OR REPLACE FUNCTION func(_tbl regclass)
  RETURNS TABLE (id int, e text[]) AS
$func$
DECLARE
   _ct int;
BEGIN
   EXECUTE format(
      'CREATE TEMP TABLE tmp ON COMMIT DROP AS
       SELECT id, tags FROM %s'
    , _tbl);

   GET DIAGNOSTICS _ct = ROW_COUNT; -- cheaper than another count(*)

   -- ANALYZE tmp;  -- if you are going to run multiple queries

   RAISE NOTICE '% results', _ct;

   RETURN QUERY TABLE tmp;
END
$func$ LANGUAGE plpgsql;

Call (note the syntax!):

SELECT * FROM func('test');

Related:

• Postgres creating a local temp table (on commit drop) from a dynamic sql string

Just a proof of concept. While you are selecting the whole table, you would just use the underlying table instead. In reality you'll have some WHERE clause in the query ...

Careful of the lurking type mismatch, count() returns bigint, you couldn't assign that to an integer variable. Would need a cast: count(*)::int.

But I replaced that completely, it's cheaper to run this right after EXECUTE:

GET DIAGNOSTICS _ct = ROW_COUNT; 

Details in the manual.

Why ANALYZE?

• Are regular VACUUM ANALYZE still recommended under 9.1?

---

Aside: CTEs in plain SQL can often do the job:

• Switching from FOR loops in plpgsql to set-based SQL commands

这篇关于如何在plpgsql中使用记录类型变量？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！