passing field values into temp variables

Question

CREATE OR REPLACE FUNCTION param_labels(_region_label text, _model_label text)
RETURNS TABLE (param_label text, param_graphics_label text) LANGUAGE sql AS
$BODY$
SELECT p.param_label, p.param_graphics_label
FROM   parameters      p 
JOIN   parameter_links l USING (param_id)
JOIN   regions         r USING (region_id)
JOIN   models          m USING (model_id)
WHERE  p.active
AND    r.region_label = $1 
AND    m.model_label = $2
ORDER  BY p.param_graphics_label;
$BODY$;

In the above function in SELECT clause I have 2 fields, now I need to pass those fields into two temp variables. Please give me your valuable suggestions.

My most valuable suggestion is for you to explain what you are trying to achieve. My second most valuable suggestion is to check the [fine manual for PL/pgSQL - SQL Procedural Language](http://www.postgresql.org/docs/current/static/plpgsql.html) — Clodoaldo Neto, May 15 '14 at 13:21

score 0 · Answer 1 · edited May 23 '17 at 12:21

There are no "variables" in an SQL function. You may be confusing that with PL/pgSQL or some other procedural language function.

In a plpgsql function You can assign multiple variables at once using SELECT INTO. Example:
Speed up plpgsql that counts doc types in a loop?

But your query does not fit the description and returns a set of rows. You may be looking for a temporary table:

CREATE TEMP TABLE tmp AS
SELECT p.param_label, p.param_graphics_label FROM ...

Example:
Using temp table in PL/pgSQL procedure for cleaning tables

passing field values into temp variables

1 Answers1