CODEWITHSUNDEEP
postgresqlplpgsql
Siavosh
Siavosh

Reputation: 2354

Data Type for select in plpgsql function and access its fields

I have the following tables in a Postgres 9.5 database:

product

 Column         |            Type             |                       Modifiers
----------------+-----------------------------+-----------------------------------------------------
id              | integer                     | not null default nextval('product_id_seq'::regclass)
name            | character varying(100)      |
number_of_items | integer                     |
created_at      | timestamp without time zone | default now()
updated_at      | timestamp without time zone | default now()
total_number    | integer                     |
provider_id     | integer                     |  
 Indexes:
    "pk_product" PRIMARY KEY, btree (id)
 Foreign-key constraints:
   "fk_product_provider" FOREIGN KEY (provider_id) REFERENCES provider(id)

And we also have

provider 

     Column    |            Typ         |           Modifiers
  -------------+------------------------+------------------------------
   id          |       integer          | not null default nextval('property_id_seq'::regclass)
   name        |       text             |
   description |       text             |
   created_at  | timestamp without time zone | default now()
   updated_at  | timestamp without time zone | default now()
 Indexes:
      "pk_provider" PRIMARY KEY, btree (id)

I am implelemtnig a plpgsql function which is supposed to find some specific products of a provider and loop through them 

      products = select u_id, number_of_items from product 
          where provider_id = p_id and total_number > limit;
      loop 
          //here I need to loop through the products 

      end loop;

Question what kind of data type should I declare for the products variables in order to store queried products into it? and also how should I have later on access to its columns like id or number_of_items?

Upvotes: 0

Views: 37

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247830

In PostgreSQL, creating table also defines a composite data type with the same name as the table.

You can either use a variable of that type:

DECLARE
   p product;
BEGIN
   FOR p IN SELECT product FROM product WHERE ...
   LOOP
      [do something with "p.id" and "p.val"]
   END LOOP;
END;

Or you can use several variables for the individual fields you need (probably better):

DECLARE
   v_id integer;
   v_val text;
BEGIN
   FOR v_id, v_val IN SELECT id, val FROM product WHERE ...
   LOOP
      [do something with "v_id" and "v_val"]
   END LOOP;
END;

Upvotes: 2

Related Questions