Officially, PostgreSQL only has "functions". Trigger functions are sometimes referred to as "trigger procedures", but that usage has no distinct meaning. Internally, functions are sometimes referred to as procedures, such as in the system catalog pg_proc
. That's a holdover from PostQUEL. Any features that some people (possibly with experience in different database systems) might associate with procedures, such as their relevance to preventing SQL injections or the use of output parameters, also apply to functions as they exist in PostgreSQL.
Now, when people in the PostgreSQL community talk about "stored procedures" or "real stored procedures", however, they often mean a hypothetical feature of a function-like object that can start and stop transactions in its body, something that current functions cannot do. The use of the term "stored procedure" in this context appears to be by analogy to other database products. See this mailing list thread for a vague idea.
In practice, however, this distinction of function versus procedure in terms of their transaction-controlling capabilities is not universally accepted, and certainly many programmers without database bias will take a Pascal-like interpretation of a procedure as a function without return value. (The SQL standard appears to take a middle ground, in that a procedure by default has a different transaction behavior than a function, but this can be adjusted per object.) So in any case, and especially when looking at questions on Stack Exchange with a very mixed audience, you should avoid assuming too much and use clearer terms or define the properties that you expect.
In PostgreSQL, every table name serves as type name for the row type (a.k.a. composite type) automatically - not a table type, there are no "table types" or "table variables" in Postgres (but there are typed tables).
So you can just declare a variable of that type in PL/pgSQL
.
CREATE FUNCTION foo()
RETURNS void LANGUAGE plpgsql AS
$func$
DECLARE
q1 foo; -- "foo" ...
q2 bar; -- ... and "bar" are existing (visible) table names
BEGIN
FOR q1 IN
SELECT * from foo
LOOP
FOR q2 IN
SELECT * from bar
LOOP
-- do something with q1 and q2
-- since q1 and q2 are well known types, you can access columns
-- with attribute notation. Like: q1.col1
END LOOP;
END LOOP;
END
$func$
A FOR
loop works with a built-in cursor. There are also explicit cursors in plpgsql.
You could also just declare variables of the generic type record
. It can take any row type at assignment automatically. But special rules apply. Be sure to follow the link and read the chapter of the manual!
While it's often convenient to have the function return SETOF <table name>
, returning SETOF record
is not as convenient. The system does not know what the function returns this way and you have to add a column definition list with every call. Which is a pain. Details about table functions in the manual.
Often there are more efficient solutions with plain SQL, though. Looping is a measure of last resort, when you can do things in one scan where you would need multiple scans in pure SQL.
Best Answer
While
RETURN NEXT
is valid plpgsql syntax,is non-existent as such. It corresponds to how you declare the return type in the function header. Read the manual about how to return from a function.RETURN RECORD
In a function declaring
RETURNS int
you can:For
RETURNS SETOF int
you can:If you declare
OUT
parameters, you can just:Or if you combine
OUT
parameters withRETURNS SETOF record
- effectively the same asRETURNS TABLE (...)
:The current state of all
OUT
parameters is returned as next row.Finally, to return a whole set of rows at once:
record
is a special data type. It's meaning depends on where you use it. Like I posted in my answer to your previous question:The manual about the
record
type in PL/pgSQL.