Concat text variables in postgresql function

Question

I have this code and I want to concatenate the variables but don't work. This is my DDL code for the view:

CREATE OR REPLACE function acd.add_credito2()
RETURNS void
SET SCHEMA 'acd'
SET search_path = acd
AS $$
DECLARE
    auxsigla text;
    auxnome text;
    _sql text := 'CREATE OR REPLACE VIEW acd.teste AS SELECT md.matriz_disciplina_id AS id, dcp.nome, mc.curso, mc.versao AS matriz';    
    _join text := ' FROM matriz_disciplina as md LEFT JOIN disciplina as dcp ON md.disciplina_id = dcp.disciplina_id LEFT JOIN matriz_curricular as mc ON md.matriz_curricular_id = mc.matriz_curricular_id';

BEGIN
    select into auxsigla, auxnome from ( select sigla, nome from acd.categoria_credito where categoria_credito_id = 9) as foo;
    _join := _join || ' LEFT JOIN (SELECT creditos, matriz_disciplina_id FROM acd.disciplina_credito WHERE categoria_credito_id = ' || x || ') AS ' || "auxsigla" ' ON ' || "auxsigla" || '.matriz_disciplina_id = md.matriz_disciplina_id';
        _sql := _sql || ', ' || "auxsigla" || '.' || auxnome || ' AS ' || auxnome;

    _sql := _sql || _join;

    EXECUTE _sql;
END;
$$ LANGUAGE plpgsql

So, when I execute the function

database-1=# select acd.add_credito2();

This error appears:

ERROR:  type "auxsigla" does not exist
LINE 1: ...WHERE categoria_credito_id = ' || x || ') AS ' || "auxsigla"...
                                                             ^
QUERY:  SELECT _join || ' LEFT JOIN (SELECT creditos, matriz_disciplina_id FROM acd.disciplina_credito WHERE categoria_credito_id = ' || x || ') AS ' || "auxsigla" ' ON ' || "auxsigla" || '.matriz_disciplina_id = md.matriz_disciplina_id'
CONTEXT:  PL/pgSQL function add_credito2() line 13 at assignment

Can anyone help me? I don't know what to do now.

(I know, this study view don't have a purpose but this is the idea that I want to use in the real view)

You should really use format() with %I for table and column names and %L for parameters to generate dynamic SQL - that will make your life a lot easier (and the code easier to read and maintain) — user330315
– user330315, Commented Sep 19, 2018 at 19:55

Laurenz Albe · Accepted Answer · 2018-09-19 20:00:00Z

1

The error comes from this construct:

"auxsigla" ' ON '

You forgot a concatenation operator || between these two tokens, and now the SQL parser interprets it as

data_type string_constant

which is a way to specify constants of a certain data type.

Working examples would be DATE '2018-09-20' or INTEGER '-20'.

Your function has numerous other problems, two of which I could spot:

select into auxsigla, auxnome from will always set the variables to NULL because you forgot to specify which columns you want to select.
you do not properly escape single quotes while composing your dynamic query string. What if auxsigla has the value with'quote?

Use format() or quote_literal() and quote_ident() for that.

answered Sep 19, 2018 at 20:00

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

1 Comment

Elton Flix de Oliveira Benevid Over a year ago

Yeah.. I realized that missing operator, thanks (and the missing columns either)

Collectives™ on Stack Overflow

Concat text variables in postgresql function

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related