Store query result in a variable using in PL pgSQL

Question

How to assign the result of a query to a variable in PL pgSQL  the procedural language of PostgreSQL   I have a function   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   In the above function I need to store the result of this query    SELECT name FROM test table where id    x    to the variable name   How to process this

User · Answer

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

name := (SELECT t.name from test_table t where t.id = x);

Or use SELECT INTO like @mu already provided.

This works, too:

name := t.name from test_table t where t.id = x;

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 IF EXISTS() instead like provided by @Pavel.

User · Answer

The usual pattern is EXISTS subselect    BEGIN   IF EXISTS SELECT name               FROM test table t              WHERE t id   x                AND t name    test     THEN      ---   ELSE      ---   END IF    This pattern is used in PL SQL  PL pgSQL  SQL PSM

User · Answer

You can use the following example to store a query result in a variable using PL pgSQL    select   into demo from maintenanceactivitytrack        raise notice p maintenanceid    demo

User · Answer

I think you re looking for SELECT INTO   select test table name into name from test table where id   x    That will pull the name from test table where id is your function s argument and leave it in the name variable  Don t leave out the table name prefix on test table name or you ll get complaints about an ambiguous reference

User · Answer

Create Learning Table    CREATE TABLE  public   learning         api id  int4 DEFAULT nextval  share api api id seq   regclass  NOT NULL       title  varchar 255  COLLATE  default          Insert Data Learning Table    INSERT INTO  public   learning  VALUES   1    Google AI-01    INSERT INTO  public   learning  VALUES   2    Google AI-02    INSERT INTO  public   learning  VALUES   3    Google AI-01         Step  01   CREATE OR REPLACE FUNCTION get all  pattern VARCHAR  RETURNS TABLE           learn id INT          learn title VARCHAR   AS    BEGIN     RETURN QUERY SELECT         api id          title     FROM         learning     WHERE         title   pattern   END      LANGUAGE  plpgsql        Step  02   SELECT   FROM get all  Google AI-01         Step  03   DROP FUNCTION get all      Demo

[database] Store query result in a variable using in PL/pgSQL

Examples related to database

Examples related to postgresql

Examples related to stored-procedures

Examples related to plpgsql

Examples related to postgresql-9.1