Fwd: ALTER PUBLICATION

[Lalith Tenneti <lalithkx@xxxxxxxxx>]

ALTER PUBLICATION ADD TABLE with table names from variable

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

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@xxxxxxxxx> 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@xxxxxxxxx> 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.g.johnston@xxxxxxxxx> wrote:

On Tuesday, October 30, 2018, Lalith Tenneti <lalithkx@xxxxxxxxx> 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.