Get rid of brackets around variable

Started by Raul Kaubiabout 6 years ago3 messagesgeneral
Jump to latest
#1Raul Kaubi
raulkaubi@gmail.com

Hi

PostgreSQL 12.1

I am trying to figure out, how can I get rid of brackets for variable.

Example as follows:

DO $$

DECLARE
cur cursor for
select * from (values('logi_web'), ('logi_taustaprotsess')) as q (col1);
BEGIN
for i in cur LOOP
RAISE NOTICE 'create table %_y2020m01 PARTITION OF % FOR VALUES FROM
(''2019-12-01'') TO (''2020-01-01'')', i, i;
END LOOP;
END;
$$ LANGUAGE plpgsql;

If I execute, this is the output:

NOTICE: create table (logi_web)_y2020m01 PARTITION OF (logi_web) FOR

VALUES FROM ('2019-12-01') TO ('2020-01-01')
NOTICE: create table (logi_taustaprotsess)_y2020m01 PARTITION OF
(logi_taustaprotsess) FOR VALUES FROM ('2019-12-01') TO ('2020-01-01')
DO

Now I have tried, even if I execute this statement:

EXECUTE 'create table '||i||'_y2020m01 PARTITION OF '||i||' FOR VALUES FROM

(''2019-12-01'') TO (''2020-01-01'')';

ERROR: syntax error at or near "("

LINE 1: create table (logi_web)_y2020m01 PARTITION OF (logi_web) FOR...
^
QUERY: create table (logi_web)_y2020m01 PARTITION OF (logi_web) FOR
VALUES FROM ('2019-12-01') TO ('2020-01-01')
CONTEXT: PL/pgSQL function inline_code_block line 8 at EXECUTE

Then you can see, that it still puts these brackets around variable.

Raul

#2Geoff Winkless
pgsqladmin@geoff.dj
In reply to: Raul Kaubi (#1)
Re: Get rid of brackets around variable

On Wed, 5 Feb 2020 at 10:48, Raul Kaubi <raulkaubi@gmail.com> wrote:

DO $$

DECLARE
cur cursor for
select * from (values('logi_web'), ('logi_taustaprotsess')) as q (col1);
BEGIN
for i in cur LOOP
RAISE NOTICE 'create table %_y2020m01 PARTITION OF % FOR VALUES FROM
(''2019-12-01'') TO (''2020-01-01'')', i, i;
END LOOP;
END;
$$ LANGUAGE plpgsql;

If I execute, this is the output:

NOTICE: create table (logi_web)_y2020m01 PARTITION OF (logi_web) FOR

VALUES FROM ('2019-12-01') TO ('2020-01-01')
NOTICE: create table (logi_taustaprotsess)_y2020m01 PARTITION OF
(logi_taustaprotsess) FOR VALUES FROM ('2019-12-01') TO ('2020-01-01')
DO

You're returning rows from the cursor.

You need to use i.col1 instead of i.

Geoff

#3Raul Kaubi
raulkaubi@gmail.com
In reply to: Geoff Winkless (#2)
Re: Get rid of brackets around variable

Awesome, thanks!

Kontakt Geoff Winkless (<pgsqladmin@geoff.dj>) kirjutas kuupäeval K, 5.
veebruar 2020 kell 13:11:

Show quoted text

On Wed, 5 Feb 2020 at 10:48, Raul Kaubi <raulkaubi@gmail.com> wrote:

DO $$

DECLARE
cur cursor for
select * from (values('logi_web'), ('logi_taustaprotsess')) as q (col1);
BEGIN
for i in cur LOOP
RAISE NOTICE 'create table %_y2020m01 PARTITION OF % FOR VALUES FROM
(''2019-12-01'') TO (''2020-01-01'')', i, i;
END LOOP;
END;
$$ LANGUAGE plpgsql;

If I execute, this is the output:

NOTICE: create table (logi_web)_y2020m01 PARTITION OF (logi_web) FOR

VALUES FROM ('2019-12-01') TO ('2020-01-01')
NOTICE: create table (logi_taustaprotsess)_y2020m01 PARTITION OF
(logi_taustaprotsess) FOR VALUES FROM ('2019-12-01') TO ('2020-01-01')
DO

You're returning rows from the cursor.

You need to use i.col1 instead of i.

Geoff