Fwd: ALTER PUBLICATION

ALTER PUBLICATION ADD TABLE with table names from variable

---------- Forwarded message ---------
From: Lalith Tenneti <lalithkx(at)gmail(dot)com>
Date: Wed, Oct 31, 2018 at 1:49 PM
Subject: Re: ALTER PUBLICATION
To: David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>

For people who need help: Here is what I did

DO
$do$
DECLARE temprow pg_tables%rowtype;
DECLARE pubname text = 'unk101';
DECLARE sqlstring text;

BEGIN
sqlstring = concat('CREATE PUBLICATION ', E'\"', pubname, E'\"');
RAISE NOTICE 'SQl: %', sqlstring;
EXECUTE sqlstring;

FOR temprow IN
SELECT *
FROM pg_tables t
WHERE t.tableowner = current_user
and schemaname = 'public'
LOOP
sqlstring = concat('ALTER PUBLICATION ', E'\"', pubname , E'\"', E' ADD
TABLE \"', temprow.tablename, E'\"');
RAISE NOTICE 'SQl: %', sqlstring;
EXECUTE sqlstring;
END LOOP;

On Wed, Oct 31, 2018 at 1:47 PM Lalith Tenneti <lalithkx(at)gmail(dot)com> wrote:

> David,
>
> got it working. Thanks for the help. My experience is with SQL server. New
> to postgres.
>
> On Wed, Oct 31, 2018 at 10:39 AM Lalith Tenneti <lalithkx(at)gmail(dot)com>
> wrote:
>
>> Hi David,
>>
>> I followed your advice and tried the following. I tried both with
>> 'format' and without. Please let me know if I am doing something wrong.
>>
>> BTW: I read the following in the docs. "Another restriction on parameter
>> symbols is that they only work in SELECT, INSERT, UPDATE, and DELETE commands.
>> In other statement types (generically called utility statements), you must
>> insert values textually even if they are just data values". If this is
>> true then we cannot use execute for LATER PUBLICATION.
>>
>> DO
>> $do$
>> DECLARE temprow pg_tables%rowtype;
>> DECLARE tbname text;
>> DECLARE dbname text = 'UNK101';
>> BEGIN
>>
>> CREATE PUBLICATION dbname;
>>
>> FOR temprow IN
>> SELECT tablename --into tb_name
>> FROM pg_tables t
>> WHERE t.tableowner = current_user
>> and schemaname = 'public'
>> and t.tablename != 'EgmFiles'
>> and t.tablename != 'EgmFileDetails'
>> and t.tablename != '__EFMigrationsHistory'
>> LOOP
>> RAISE NOTICE 'Table Name: %', temprow.tablename;
>> tbname = CAST(temprow.tablename as text);
>> --ALTER PUBLICATION UNK101 ADD TABLE tbname;
>> EXECUTE format('ALTER PUBLICATION $1 ADD TABLE $2;') using dbname, tbname;
>> END LOOP;
>> END
>> $do$ LANGUAGE plpgsql;
>>
>>
>> On Tue, Oct 30, 2018 at 5:35 PM David G. Johnston <
>> david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>>
>>> On Tuesday, October 30, 2018, Lalith Tenneti <lalithkx(at)gmail(dot)com> wrote:
>>>
>>>> Hi All,
>>>>
>>>> I am trying to run ALTER PUBLICATION ABC ADD TABLE <variable>. The
>>>> variable is being set in a SELECT statement beforehand. But the ALTER
>>>> statement adds the literal variable name. Is there anyway to acheive this?
>>>> The reason is I do not want to hard code the table names.
>>>>
>>>> =============
>>>>
>>>> SQL doesn’t have variables and <variable> is not valid psql syntax. If
>>>> you are doing this in pl/pgsql you will need to use EXECUTE and dynamic SQL
>>>> and inject the variable into the statement string using format(stmt,
>>>> variable) where stmt = “... ADD TABLE %I”
>>>>
>>>> David J.
>>>>
>>>>