9

I have the following function that takes a table name as parameter and returns the table:

CREATE FUNCTION func(tab_name varchar) RETURNS TABLE (
    col text,
    col2 text
) AS $$
BEGIN
    RETURN QUERY 
        EXECUTE format('SELECT * FROM %I', tab_name);
END;
$$
LANGUAGE plpgsql;

Suppose instead, I wanted to do return the table 100 times:

CREATE FUNCTION func(tab_name varchar) RETURNS TABLE (
    col text,
    col2 text
) AS $$
BEGIN
    RETURN QUERY 
        EXECUTE format('
           SELECT * FROM %I
           UNION ALL
           SELECT * FROM %I,
           UNION ALL
           SELECT * FROM %I
           ......
        ', tab_name, tab_name, tab_name,.....);
END;
$$
LANGUAGE plpgsql;

Instead of putting tab_name 100 times is there some way to use named parameters?

Improve this question

1 Answer 1

Reset to default
14

You can indicate the position of an argument and use an argument multiple times, e.g.

select format('%2$s - %1$s - %2$s', 'first', 'second');

         format          
-------------------------
 second - first - second
(1 row)

Read more in the documentation.

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.