blocks|key|482110|text|我想你是在找SELECT+select_expressions+INTO|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|482111|select+test_table.name+into+name+from+test_table+where+id+=+x;|code-block|syntax|javascript|482112|这将从test_table中提取name，其中id是函数的参数，并将其保留在name变量中。不要遗漏了test_table.name上的表名前缀，否则您会收到关于引用不明确的投诉。|482113|entityMap|0|LINK|mutability|MUTABLE|url|http://www.postgresql.org/docs/current/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW^0|6|U|6|U|0|0|0|3|A|G|4|N|2|12|4|1F|F|0^^$0|@$1|2|3|4|5|6|7|U|8|@$9|V|A|W|B|C]]|D|@$9|X|A|Y|1|Z]]|E|$]]|$1|F|3|G|5|H|7|10|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|11|8|@$9|12|A|13|B|C]|$9|14|A|15|B|C]|$9|16|A|17|B|C]|$9|18|A|19|B|C]|$9|1A|A|1B|B|C]]|D|@]|E|$]]|$1|M|3|-4|5|6|7|1C|8|@]|D|@]|E|$]]]|N|$O|$5|P|Q|R|E|$S|T]]]]

I think you're looking for <a href="http://www.postgresql.org/docs/current/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW" rel="noreferrer"><code>SELECT select_expressions INTO</code></a>:
<pre><code>select test_table.name into name from test_table where id = x;
</code></pre>
That will pull the <code>name</code> from <code>test_table</code> where <code>id</code> is your function's argument and leave it in the <code>name</code> variable. Don't leave out the table name prefix on <code>test_table.name</code> or you'll get complaints about an ambiguous reference.

blocks|key|265316|text|只要你赋值的是一个变量，你也可以在plpgsql函数中使用普通赋值：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|265317|name+:=+(SELECT+t.name+from+test_table+t+where+t.id+=+x);|code-block|syntax|javascript|265318|或者像@mu+already+provided一样使用SELECT+INTO。|offset|length|style|CODE|265319|这也是可行的：|265320|name+:=+t.name+from+test_table+t+where+t.id+=+x;|265321|但最好使用前两种方法中的一种，更清晰的方法，就像@Pavel评论的那样。|265322|我另外使用了一个表别名来缩短语法。|265323|更新:我删除了我的代码示例，建议使用像provided+by+@Pavel一样的IF+EXISTS()。|265324|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/a/12328286/939860|1|https://stackoverflow.com/a/12333544/939860^0|0|0|R|B|3|K|0|0|0|0|0|0|14|B|J|I|1|0^^$0|@$1|2|3|4|5|6|7|16|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|17|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|18|8|@$I|19|J|1A|K|L]]|9|@$I|1B|J|1C|1|1D]]|A|$]]|$1|M|3|N|5|6|7|1E|8|@]|9|@]|A|$]]|$1|O|3|P|5|D|7|1F|8|@]|9|@]|A|$E|F]]|$1|Q|3|R|5|6|7|1G|8|@]|9|@]|A|$]]|$1|S|3|T|5|6|7|1H|8|@]|9|@]|A|$]]|$1|U|3|V|5|6|7|1I|8|@$I|1J|J|1K|K|L]]|9|@$I|1L|J|1M|1|1N]]|A|$]]|$1|W|3|-4|5|6|7|1O|8|@]|9|@]|A|$]]]|X|$Y|$5|Z|10|11|A|$12|13]]|14|$5|Z|10|11|A|$12|15]]]]

As long as you are assigning a single variable, you can also use plain assignment in a plpgsql function:

<pre><code>name := (SELECT t.name from test_table t where t.id = x);
</code></pre>

Or use <code>SELECT INTO</code> like <a href="https://stackoverflow.com/a/12328286/939860">@mu already provided</a>.

This works, too:

<pre><code>name := t.name from test_table t where t.id = x;
</code></pre>

But better use one of the first two, clearer methods, as @Pavel commented.

I shortened the syntax with a table alias additionally. 
Update: I removed my code example and suggest to use <code>IF EXISTS()</code> instead like <a href="https://stackoverflow.com/a/12333544/939860">provided by @Pavel</a>.

blocks|key|265378|text|通常的模式是EXISTS(subselect)|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|265379|BEGIN
++IF+EXISTS(SELECT+name
++++++++++++++FROM+test_table+t
+++++++++++++WHERE+t.id+=+x
+++++++++++++++AND+t.name+=+'test')
++THEN
+++++---
++ELSE
+++++---
++END+IF;|code-block|syntax|javascript|265380|此模式用于PL/SQL、PL/pgSQL、SQL/PSM、...|265381|entityMap^0|6|H|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|R|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|S|8|@]|D|@]|E|$]]|$1|M|3|-4|5|6|7|T|8|@]|D|@]|E|$]]]|N|$]]

The usual pattern is <code>EXISTS(subselect)</code>:

<pre><code>BEGIN
 IF EXISTS(SELECT name
 FROM test_table t
 WHERE t.id = x
 AND t.name = 'test')
 THEN
 ---
 ELSE
 ---
 END IF;
</code></pre>

This pattern is used in PL/SQL, PL/pgSQL, SQL/PSM, ...

blocks|key|265422|text|您可以使用以下示例将查询结果存储在使用PL/pgSQL的变量中：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|265423|+select+*+into+demo+from+maintenanceactivitytrack+;+
++++raise+notice'p_maintenanceid:%25',demo;|code-block|syntax|javascript|265424|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

You can use the following example to store a query result in a variable using PL/pgSQL:

<pre><code> select * into demo from maintenanceactivitytrack ; 
 raise notice'p_maintenanceid:%',demo;
</code></pre>

blocks|key|265612|text|对于每个Executing+a+Query+with+a+Single-Row+Result，请使用以下语法：|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|265613|SELECT+select_expressions+INTO+[STRICT]+target+FROM+...|code-block|syntax|javascript|265614|其中，target可以是记录变量、行变量或简单变量和记录/行字段的逗号分隔列表。|style|CODE|265615|与SELECT+INTO不同，SELECT+select_expressions+INTO不创建表。|265616|在您的示例中，您只有一个简单的变量name，因此select语句将为：|265617|SELECT+test_table.name+INTO+name+FROM+test_table+WHERE+test_table.id+=+x;|265618|entityMap|0|LINK|mutability|MUTABLE|url|https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW^0|4|16|0|0|0|3|6|0|1|B|F|U|0|H|4|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@]|9|@$A|11|B|12|1|13]]|C|$]]|$1|D|3|E|5|F|7|14|8|@]|9|@]|C|$G|H]]|$1|I|3|J|5|6|7|15|8|@$A|16|B|17|K|L]]|9|@]|C|$]]|$1|M|3|N|5|6|7|18|8|@$A|19|B|1A|K|L]|$A|1B|B|1C|K|L]]|9|@]|C|$]]|$1|O|3|P|5|6|7|1D|8|@$A|1E|B|1F|K|L]]|9|@]|C|$]]|$1|Q|3|R|5|F|7|1G|8|@]|9|@]|C|$G|H]]|$1|S|3|-4|5|6|7|1H|8|@]|9|@]|C|$]]]|T|$U|$5|V|W|X|C|$Y|Z]]]]

Per <a href="https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW" rel="nofollow noreferrer">Executing a Query with a Single-Row Result</a>, use this syntax:
<pre class="lang-sql prettyprint-override"><code>SELECT select_expressions INTO [STRICT] target FROM ...
</code></pre>
where <code>target</code> can be a record variable, a row variable, or a comma-separated list of simple variables and record/row fields.
Unlike the <code>SELECT INTO</code>, <code>SELECT select_expressions INTO</code> does not create a table.
In your example, you have a single simple variable <code>name</code>, so the select statement would be:
<pre class="lang-sql prettyprint-override"><code>SELECT test_table.name INTO name FROM test_table WHERE test_table.id = x;
</code></pre>

How to assign the result of a query to a variable in PL/pgSQL, the procedural language of PostgreSQL?

I have a function:

<pre><code>CREATE OR REPLACE FUNCTION test(x numeric)
RETURNS character varying AS
$BODY$
DECLARE
name character varying(255);
begin
 name ='SELECT name FROM test_table where id='||x;

 if(name='test')then
 --do somthing
 else
 --do the else part
 end if;
end;
return -- return my process result here
$BODY$
LANGUAGE plpgsql VOLATILE
</code></pre>

In the above function I need to store the result of this query:

<pre><code>'SELECT name FROM test_table where id='||x;
</code></pre>

to the variable <code>name</code>.

How to process this?

Store query result in a variable using in PL/pgSQL

PostgreSQL

在PostgreSQL的过程化语言PL/pgSQL中，如何将查询结果赋值给变量？我有一个函数：CREATE OR REPLACE FUNCTION test(x numeric)RETURNS character varying AS$BODY$DECLAREname   character varying(255);...

问使用PL/pgSQL将查询结果存储在变量中
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用PL/pgSQL将查询结果存储在变量中EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用PL/pgSQL将查询结果存储在变量中
EN