Variables in PostgreSQL loops

Question

I have the following function (returns a set of custom types)

CREATE OR REPLACE FUNCTION myfunction(in_myvar integer)
  RETURNS SETOF my_type AS
$BODY$

DECLARE
    v_data my_type%rowtype;
    v_another_var text;
BEGIN
    FOR v_data IN (

        -- I want to be able to do a SELECT INTO v_another_var in each iteration
        -- of the loop, ideally so I can use this value throughout the select
        -- statement e.g.:
        -- SELECT INTO v_another_var regex_replace(col3, '[^a-zA-Z0-9 ]', '');

        SELECT DISTINCT 
            col1
            col2,
            -- v_another_var AS some_column
            -- etc.
            -- perhaps use this v_another_var again here
            -- as part of something else.
          FROM mytable
          WHERE v_another_var = 'test' + some_function() = 'something'
                    -- want to use the variable again here.
    ) LOOP
        RETURN NEXT v_data;
    END LOOP;
    RETURN;
$BODY$

Is it possible to assign a variable and use it in each select statement as part of a loop in this way? Even if this is possible, would the SELECT INTO statement work where I have it (would col3 be accessible?)

Is there a way in postgres to achieve this kind of variable usage?

kgrittn · Accepted Answer · 2012-04-20 12:44:19Z

3

You need to SELECT the value for v_another_var in the query controlling you loop, or inside the LOOP/END LOOP construct, if you want a different value in each iteration of the loop. You can always have additional queries to assign other values inside the loop if needed.

It's hard to make a more concrete suggestion without a more concrete example.

answered Apr 20, 2012 at 12:44

kgrittn

19.8k4 gold badges41 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Erwin Brandstetter · Accepted Answer · 2012-04-20 19:19:39Z

Your question is a mess. Much of it does not make any sense at all.

Is it possible to assign a variable and use it in each select statement as part of a loop in this way?

You only have one SELECT statement in your question. You are not making sense.
There is no loop "in" a SELECT statement. You can loop through the rows resulting from a SELECT statement.

Even if this is possible, would the INSERT INTO statement work where I have it (would col3 be accessible?)

It would not at all. Neither where you have it nor what you have there.
- Maybe you meant SELECT INTO?
- There is no FROM clause, col3 is undefined.
- Only one statement in the FOR loop. More about that further down.

Furthermore ...

No parenthesis required for the SELECT of a FOR loop.
regexp_replace(), not regex_replace().
Text concatenation in PostgreSQL: 'test' || some_function()
not 'test' + some_function()
You need parentheses around WHERE x = (a || b)
Language declaration is missing. Add language plpgsql at the end.

What you may be asking (wild guess): Yes, you can assign multiple variables in a FOR loop. I quote the manual here:

The target is a record variable, row variable, or comma-separated list of scalar variables.

You cannot mix a record variable and a scalar variable, though, as you seem to be trying. You would have to ...

a) split up the composite type v_data into scalar variables and list them in order, then append v_another_var to the FOR target list.

or b) add a text column (instead of v_another_var) to the composite type my_type. However, you would have to reorganize the RETURN NEXT statement in this case. Since I am just guessing what your question might be, I will not go into more details.

Clean up your question and ask a precise question if you want a precise answer.

Wow sorry. I've not deliberately tried to make this question complicated/messy - I guess I don't really understand how this works all that well.

Collectives™ on Stack Overflow

Variables in PostgreSQL loops

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related