How to use string with apostrophe in function variable plpgsql

Question

Hello I am having trouble querying when I have apostrophe in my where clause in postgresql using pgpsql function, I know that manually I could do something like:

select 'author''s'

however my word is stored in a variable, here is my function:

CREATE OR REPLACE FUNCTION public.fn_inserir_doc(caminho_arqv text, conteudo text)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
    declare
        conteudo_array text array;
        palavra text;
    begin
        execute 'insert into documento(caminho)
                    select ''' || caminho_arqv || '''
                    where not exists(select id 
                                    from documento
                                    where caminho='''||caminho_arqv||''')';
        conteudo_array := regexp_split_to_array(conteudo, E'\\s+');
        FOREACH palavra in array conteudo_array
        loop
              if length(palavra) >=3 then
                raise notice 'palavra: %', palavra;
                execute 'insert into termo(descricao)
                            select ''' || palavra || '''
                            where not exists(
                                            select id from termo
                                            where descricao='''||palavra||''')';
                execute 'insert into documento_termo(id_termo, id_documento, frequencia)
                            select t.id, d.id, 1
                            from termo t
                            cross join documento d
                            where t.descricao = '''|| palavra ||'''
                            and d.caminho = '''|| caminho_arqv ||'''
                            on conflict (id_termo, id_documento) do update set frequencia = documento_termo.frequencia + 1;';
                 end if;
        end loop;
    end;
$function$

The following sample is the one that has the problem:

select id from termo
where descricao='''||palavra||'''

because palavra contains single quote

klin · Accepted Answer · 2017-06-26 01:50:37Z

1

Use dollar quoting and the function format(). Example:

create or replace function test(str text)
returns setof text language plpgsql as $$
begin
-- instead of this:
--  return query execute 'select '''||str||'''::text';
-- use:
    return query execute format(
        $fmt$
            select %L::text
        $fmt$, str);
end $$;

select * from test('O''Brian');

  test   
---------
 O'Brian
(1 row)

answered Jun 26, 2017 at 1:50

klin

123k15 gold badges240 silver badges262 bronze badges

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

Collectives™ on Stack Overflow

How to use string with apostrophe in function variable plpgsql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related