Re: [GENERAL] Clone PostgreSQL schema

2017-04-18 Thread Melvin Davidson 
On Tue, Apr 18, 2017 at 3:48 AM, R. Reiterer  wrote:

> Hi Melvin,
>
> after a first test, the function seems to work perfect! MANY THX!!!
>
> Regards,
>
> Reinhard
>
> Am 17.04.2017 17:21 schrieb Melvin Davidson:
>
>> On Mon, Apr 17, 2017 at 11:02 AM, Melvin Davidson
>>  wrote:
>>
>> On Mon, Apr 17, 2017 at 9:42 AM, Melvin Davidson
>>>  wrote:
>>>
>>> On Mon, Apr 17, 2017 at 2:20 AM, R. Reiterer 
>>> wrote:
>>> Hi Melvin,
>>>
>>> thanks again for your help! I did some testing, but views in the
>>> new schema still refer to the old schema.
>>>
>>> Regards, Reinhard
>>>
>>> Am 17.04.2017 04:53 schrieb Melvin Davidson:
>>> On Sun, Apr 16, 2017 at 4:42 PM, R. Reiterer 
>>> wrote:
>>>
>>> Unfortunately, I do not have the skills to improve the function.
>>> Maybe someone at dba.stackexchange.com [1] [1] can help me. I'll
>>>
>>> open a
>>>
>>> ticket. I hope this is okay for you.
>>>
>>> Am 16.04.2017 22:31 schrieb Melvin Davidson:
>>> I missed to note that this is a VIEW issue (?)
>>> AH, IN THAT CASE, YOU ARE RIGHT. I AM RETIRED, BUT IF YOU WISH, GO
>>> AHEAD AND APPLY THE FIX YOURSELF. JUST MAKE A COMMENT AT THE TOP TO
>>> INCLUDE THE DATE, YOUR NAME AND THE FIX.
>>>
>>> On Sun, Apr 16, 2017 at 4:24 PM, R. Reiterer 
>>> wrote:
>>>
>>> Hi Melvin,
>>>
>>> thanks for your reply. I missed to note that this is a VIEW issue
>>> (?). After duplicating a schema, views in the cloned schema
>>> (schema_new) refer still to the source schema (schema_old) in the
>>> FROM clause:
>>>
>>> View in cloned schema (schema_new) -->
>>>
>>> CREATE VIEW schema_new.my_view AS
>>> SELECT *
>>> FROM schema_old.my_table;
>>>
>>> To me 'FROM schema_new.my_table' would be more logical.
>>>
>>> Regards, Reinhard
>>>
>>> Am 16.04.2017 22:12 schrieb Melvin Davidson:
>>> On Sun, Apr 16, 2017 at 3:20 PM, R. Reiterer 
>>> wrote:
>>>
>>> Hi Melvin,
>>>
>>> I use your PL/pgSQL function posted at
>>>
>>>
>>> https://www.postgresql.org/message-id/CANu8FixK9P8UD43nv2s%
>> 2Bc-9jHkyy8wPUc_f5K3HcrmJYVPr-wQ%40mail.gmail.com
>>
>>> [2]
>>> [2]
>>>
>>> [1]
>>> [1] to clone schemas in PostgreSQL databases. Many thanks for your
>>> work!
>>>
>>> I noticed that in cloned schemas the schema name isn't updated in
>>> the FROM clause:
>>>
>>> schema_old -->
>>>
>>> CREATE VIEW schema_old.my_view AS
>>> SELECT *
>>> FROM schema_old.my_table;
>>>
>>> schema_new -->
>>>
>>> CREATE VIEW schema_new.my_view AS
>>> SELECT *
>>> FROM schema_old.my_table;
>>>
>>> Are you interessted to fix this?
>>>
>>> Regards,
>>>
>>> Reinhard
>>>
>>> FIRST, THANK YOU FOR THE COMPLEMENT.
>>>
>>> However, AFAIC, there is nothing to "fix" with regards to cloning
>>> schema name. In a database, you cannot have two schemas with the
>>> same
>>> name,
>>>
>>> so what would be the point? If you want to "clone" to a different
>>> database, then just use pg_dump and pg_restore.
>>>
>>> --
>>>
>>> MELVIN DAVIDSON
>>> I reserve the right to fantasize. Whether or not you
>>> wish to share my fantasy is entirely up to you.
>>>
>>> Links:
>>> --
>>> [1]
>>>
>>>
>>> https://www.postgresql.org/message-id/CANu8FixK9P8UD43nv2s%
>> 2Bc-9jHkyy8wPUc_f5K3HcrmJYVPr-wQ%40mail.gmail.com
>>
>>> [2]
>>> [2]
>>> [1]
>>>
>>> --
>>>
>>> MELVIN DAVIDSON
>>> I reserve the right to fantasize. Whether or not you
>>> wish to share my fantasy is entirely up to you.
>>>
>>> Links:
>>> --
>>> [1]
>>>
>>>
>>> https://www.postgresql.org/message-id/CANu8FixK9P8UD43nv2s%
>> 2Bc-9jHkyy8wPUc_f5K3HcrmJYVPr-wQ%40mail.gmail.com
>>
>>> [2]
>>> [2]
>>>
>>> Reinhard,
>>>
>>> After reviewing things, I note it's possible that you downloaded an
>>> earlier version that had some errors in it and was not as complete.
>>>
>>> Therefore, I've attached the latest, more complete version of the
>>> function. Please let me know if this solves the problem.
>>>
>>> --
>>>
>>> MELVIN DAVIDSON
>>> I reserve the right to fantasize. Whether or not you
>>> wish to share my fantasy is entirely up to you.
>>>
>>> Links:
>>> --
>>> [1] http://dba.stackexchange.com [1]
>>> [2]
>>>
>>> https://www.postgresql.org/message-id/CANu8FixK9P8UD43nv2s%
>> 2Bc-9jHkyy8wPUc_f5K3HcrmJYVPr-wQ%40mail.gmail.com
>>
>>> [2]
>>>
>>
>> My apologies,
>>
>> I though I had had a fix. I even worked on it a couple of hours this
>> morning, but it seems it's a bit trickier than I thought. I'll keep
>> trying
>>
>> until I get it right.
>>
>> --
>>
>> MELVIN DAVIDSON
>> I reserve the right to fantasize.  Whether or not you
>>  wish to share my fantasy is entirely up to you.
>>
>> OK REINHARD, I THINK I HAVE IT, PLEASE TRY THE REVISION I HAVE
>> ATTACHED.
>>
>> --
>>
>> MELVIN DAVIDSON
>> I reserve the right to fantasize.  Whether or not you
>>  wish to share my fantasy is entirely up to you.
>>
>> OOPS, I FORGOT TO REMOVE THE PREMATURE RETURN, USE THIS LATEST
>> ATTACHED.
>>
>> --
>>
>> MELVIN DAVIDSON
>> I

Re: [GENERAL] Clone PostgreSQL schema

2017-04-17 Thread Melvin Davidson 
On Mon, Apr 17, 2017 at 11:02 AM, Melvin Davidson 
wrote:

>
>
> On Mon, Apr 17, 2017 at 9:42 AM, Melvin Davidson 
> wrote:
>
>>
>> On Mon, Apr 17, 2017 at 2:20 AM, R. Reiterer 
>> wrote:
>>
>>> Hi Melvin,
>>>
>>> thanks again for your help! I did some testing, but views in the new
>>> schema still refer to the old schema.
>>>
>>> Regards, Reinhard
>>>
>>> Am 17.04.2017 04:53 schrieb Melvin Davidson:
>>>
 On Sun, Apr 16, 2017 at 4:42 PM, R. Reiterer 
 wrote:

 Unfortunately, I do not have the skills to improve the function.
> Maybe someone at dba.stackexchange.com [1] can help me. I'll open a
>
> ticket. I hope this is okay for you.
>
> Am 16.04.2017 22:31 schrieb Melvin Davidson:
> I missed to note that this is a VIEW issue (?)
> AH, IN THAT CASE, YOU ARE RIGHT. I AM RETIRED, BUT IF YOU WISH, GO
> AHEAD AND APPLY THE FIX YOURSELF. JUST MAKE A COMMENT AT THE TOP TO
> INCLUDE THE DATE, YOUR NAME AND THE FIX.
>
> On Sun, Apr 16, 2017 at 4:24 PM, R. Reiterer 
> wrote:
>
> Hi Melvin,
>
> thanks for your reply. I missed to note that this is a VIEW issue
> (?). After duplicating a schema, views in the cloned schema
> (schema_new) refer still to the source schema (schema_old) in the
> FROM clause:
>
> View in cloned schema (schema_new) -->
>
> CREATE VIEW schema_new.my_view AS
> SELECT *
> FROM schema_old.my_table;
>
> To me 'FROM schema_new.my_table' would be more logical.
>
> Regards, Reinhard
>
> Am 16.04.2017 22:12 schrieb Melvin Davidson:
> On Sun, Apr 16, 2017 at 3:20 PM, R. Reiterer 
> wrote:
>
> Hi Melvin,
>
> I use your PL/pgSQL function posted at
>
>
> https://www.postgresql.org/message-id/CANu8FixK9P8UD43nv2s%2
 Bc-9jHkyy8wPUc_f5K3HcrmJYVPr-wQ%40mail.gmail.com

> [2]
>
> [1]
> [1] to clone schemas in PostgreSQL databases. Many thanks for your
> work!
>
> I noticed that in cloned schemas the schema name isn't updated in
> the FROM clause:
>
> schema_old -->
>
> CREATE VIEW schema_old.my_view AS
> SELECT *
> FROM schema_old.my_table;
>
> schema_new -->
>
> CREATE VIEW schema_new.my_view AS
> SELECT *
> FROM schema_old.my_table;
>
> Are you interessted to fix this?
>
> Regards,
>
> Reinhard
>
> FIRST, THANK YOU FOR THE COMPLEMENT.
>
> However, AFAIC, there is nothing to "fix" with regards to cloning
> schema name. In a database, you cannot have two schemas with the
> same
> name,
>
> so what would be the point? If you want to "clone" to a different
> database, then just use pg_dump and pg_restore.
>
> --
>
> MELVIN DAVIDSON
> I reserve the right to fantasize. Whether or not you
> wish to share my fantasy is entirely up to you.
>
> Links:
> --
> [1]
>
>
> https://www.postgresql.org/message-id/CANu8FixK9P8UD43nv2s%2
 Bc-9jHkyy8wPUc_f5K3HcrmJYVPr-wQ%40mail.gmail.com

> [2]
> [1]
>
> --
>
> MELVIN DAVIDSON
> I reserve the right to fantasize. Whether or not you
> wish to share my fantasy is entirely up to you.
>
> Links:
> --
> [1]
>
> https://www.postgresql.org/message-id/CANu8FixK9P8UD43nv2s%2
 Bc-9jHkyy8wPUc_f5K3HcrmJYVPr-wQ%40mail.gmail.com

> [2]
>

 Reinhard,

 After reviewing things, I note it's possible that you downloaded an
 earlier version that had some errors in it and was not as complete.

 Therefore, I've attached the latest, more complete version of the
 function. Please let me know if this solves the problem.

 --

 MELVIN DAVIDSON
 I reserve the right to fantasize.  Whether or not you
  wish to share my fantasy is entirely up to you.


 Links:
 --
 [1] http://dba.stackexchange.com
 [2]
 https://www.postgresql.org/message-id/CANu8FixK9P8UD43nv2s%2
 Bc-9jHkyy8wPUc_f5K3HcrmJYVPr-wQ%40mail.gmail.com

>>>
>>
>>
>> *My apologies,*
>>
>> *I though I had had a fix. I even worked on it a couple of hours this
>> morning, but it seems it's a bit trickier than I thought. I'll keep trying*
>>
>> *until I get it right.*
>>
>> --
>> *Melvin Davidson*
>> I reserve the right to fantasize.  Whether or not you
>> wish to share my fantasy is entirely up to you.
>>
>
> *OK Reinhard, I think I have it, please try the revision I have attached.*
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.
>

*Oops, I forgot to remove the premature RETURN, use this latest attached*.

-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish

Re: [GENERAL] Clone PostgreSQL schema

2017-04-17 Thread Melvin Davidson 
On Mon, Apr 17, 2017 at 9:42 AM, Melvin Davidson 
wrote:

>
> On Mon, Apr 17, 2017 at 2:20 AM, R. Reiterer  wrote:
>
>> Hi Melvin,
>>
>> thanks again for your help! I did some testing, but views in the new
>> schema still refer to the old schema.
>>
>> Regards, Reinhard
>>
>> Am 17.04.2017 04:53 schrieb Melvin Davidson:
>>
>>> On Sun, Apr 16, 2017 at 4:42 PM, R. Reiterer 
>>> wrote:
>>>
>>> Unfortunately, I do not have the skills to improve the function.
 Maybe someone at dba.stackexchange.com [1] can help me. I'll open a

 ticket. I hope this is okay for you.

 Am 16.04.2017 22:31 schrieb Melvin Davidson:
 I missed to note that this is a VIEW issue (?)
 AH, IN THAT CASE, YOU ARE RIGHT. I AM RETIRED, BUT IF YOU WISH, GO
 AHEAD AND APPLY THE FIX YOURSELF. JUST MAKE A COMMENT AT THE TOP TO
 INCLUDE THE DATE, YOUR NAME AND THE FIX.

 On Sun, Apr 16, 2017 at 4:24 PM, R. Reiterer 
 wrote:

 Hi Melvin,

 thanks for your reply. I missed to note that this is a VIEW issue
 (?). After duplicating a schema, views in the cloned schema
 (schema_new) refer still to the source schema (schema_old) in the
 FROM clause:

 View in cloned schema (schema_new) -->

 CREATE VIEW schema_new.my_view AS
 SELECT *
 FROM schema_old.my_table;

 To me 'FROM schema_new.my_table' would be more logical.

 Regards, Reinhard

 Am 16.04.2017 22:12 schrieb Melvin Davidson:
 On Sun, Apr 16, 2017 at 3:20 PM, R. Reiterer 
 wrote:

 Hi Melvin,

 I use your PL/pgSQL function posted at


 https://www.postgresql.org/message-id/CANu8FixK9P8UD43nv2s%2
>>> Bc-9jHkyy8wPUc_f5K3HcrmJYVPr-wQ%40mail.gmail.com
>>>
 [2]

 [1]
 [1] to clone schemas in PostgreSQL databases. Many thanks for your
 work!

 I noticed that in cloned schemas the schema name isn't updated in
 the FROM clause:

 schema_old -->

 CREATE VIEW schema_old.my_view AS
 SELECT *
 FROM schema_old.my_table;

 schema_new -->

 CREATE VIEW schema_new.my_view AS
 SELECT *
 FROM schema_old.my_table;

 Are you interessted to fix this?

 Regards,

 Reinhard

 FIRST, THANK YOU FOR THE COMPLEMENT.

 However, AFAIC, there is nothing to "fix" with regards to cloning
 schema name. In a database, you cannot have two schemas with the
 same
 name,

 so what would be the point? If you want to "clone" to a different
 database, then just use pg_dump and pg_restore.

 --

 MELVIN DAVIDSON
 I reserve the right to fantasize. Whether or not you
 wish to share my fantasy is entirely up to you.

 Links:
 --
 [1]


 https://www.postgresql.org/message-id/CANu8FixK9P8UD43nv2s%2
>>> Bc-9jHkyy8wPUc_f5K3HcrmJYVPr-wQ%40mail.gmail.com
>>>
 [2]
 [1]

 --

 MELVIN DAVIDSON
 I reserve the right to fantasize. Whether or not you
 wish to share my fantasy is entirely up to you.

 Links:
 --
 [1]

 https://www.postgresql.org/message-id/CANu8FixK9P8UD43nv2s%2
>>> Bc-9jHkyy8wPUc_f5K3HcrmJYVPr-wQ%40mail.gmail.com
>>>
 [2]

>>>
>>> Reinhard,
>>>
>>> After reviewing things, I note it's possible that you downloaded an
>>> earlier version that had some errors in it and was not as complete.
>>>
>>> Therefore, I've attached the latest, more complete version of the
>>> function. Please let me know if this solves the problem.
>>>
>>> --
>>>
>>> MELVIN DAVIDSON
>>> I reserve the right to fantasize.  Whether or not you
>>>  wish to share my fantasy is entirely up to you.
>>>
>>>
>>> Links:
>>> --
>>> [1] http://dba.stackexchange.com
>>> [2]
>>> https://www.postgresql.org/message-id/CANu8FixK9P8UD43nv2s%2
>>> Bc-9jHkyy8wPUc_f5K3HcrmJYVPr-wQ%40mail.gmail.com
>>>
>>
>
>
> *My apologies,*
>
> *I though I had had a fix. I even worked on it a couple of hours this
> morning, but it seems it's a bit trickier than I thought. I'll keep trying*
>
> *until I get it right.*
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.
>

*OK Reinhard, I think I have it, please try the revision I have attached.*

-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.
-- Function: public.clone_schema(text, text, boolean)

-- DROP FUNCTION public.clone_schema(text, text, boolean);

CREATE OR REPLACE FUNCTION public.clone_schema(
source_schema text,
dest_schema text,
include_recs boolean)
  RETURNS void AS
$BODY$
--  Initial code by Emanuel '3manuek' 
--  Last revision 2017-04-17 by Melvin Davidson
--  Added SELECT REPLACE for schema views
--
--  This function will clone all

Re: [GENERAL] Clone PostgreSQL schema

2017-04-16 Thread Melvin Davidson 
On Sun, Apr 16, 2017 at 4:42 PM, R. Reiterer  wrote:

> Unfortunately, I do not have the skills to improve the function. Maybe
> someone at dba.stackexchange.com can help me. I'll open a ticket. I hope
> this is okay for you.
>
> Am 16.04.2017 22:31 schrieb Melvin Davidson:
>
>> I missed to note that this is a VIEW issue (?)
>>>
>> AH, IN THAT CASE, YOU ARE RIGHT. I AM RETIRED, BUT IF YOU WISH, GO
>> AHEAD AND APPLY THE FIX YOURSELF. JUST MAKE A COMMENT AT THE TOP TO
>> INCLUDE THE DATE, YOUR NAME AND THE FIX.
>>
>>
>> On Sun, Apr 16, 2017 at 4:24 PM, R. Reiterer 
>> wrote:
>>
>> Hi Melvin,
>>>
>>> thanks for your reply. I missed to note that this is a VIEW issue
>>> (?). After duplicating a schema, views in the cloned schema
>>> (schema_new) refer still to the source schema (schema_old) in the
>>> FROM clause:
>>>
>>> View in cloned schema (schema_new) -->
>>>
>>> CREATE VIEW schema_new.my_view AS
>>> SELECT *
>>> FROM schema_old.my_table;
>>>
>>> To me 'FROM schema_new.my_table' would be more logical.
>>>
>>> Regards, Reinhard
>>>
>>> Am 16.04.2017 22:12 schrieb Melvin Davidson:
>>> On Sun, Apr 16, 2017 at 3:20 PM, R. Reiterer 
>>> wrote:
>>>
>>> Hi Melvin,
>>>
>>> I use your PL/pgSQL function posted at
>>>
>>>
>>> https://www.postgresql.org/message-id/CANu8FixK9P8UD43nv2s%
>> 2Bc-9jHkyy8wPUc_f5K3HcrmJYVPr-wQ%40mail.gmail.com
>>
>>> [1]
>>> [1] to clone schemas in PostgreSQL databases. Many thanks for your
>>> work!
>>>
>>> I noticed that in cloned schemas the schema name isn't updated in
>>> the FROM clause:
>>>
>>> schema_old -->
>>>
>>> CREATE VIEW schema_old.my_view AS
>>> SELECT *
>>> FROM schema_old.my_table;
>>>
>>> schema_new -->
>>>
>>> CREATE VIEW schema_new.my_view AS
>>> SELECT *
>>> FROM schema_old.my_table;
>>>
>>> Are you interessted to fix this?
>>>
>>> Regards,
>>>
>>> Reinhard
>>>
>>> FIRST, THANK YOU FOR THE COMPLEMENT.
>>>
>>> However, AFAIC, there is nothing to "fix" with regards to cloning
>>> schema name. In a database, you cannot have two schemas with the
>>> same
>>> name,
>>>
>>> so what would be the point? If you want to "clone" to a different
>>> database, then just use pg_dump and pg_restore.
>>>
>>> --
>>>
>>> MELVIN DAVIDSON
>>> I reserve the right to fantasize. Whether or not you
>>> wish to share my fantasy is entirely up to you.
>>>
>>> Links:
>>> --
>>> [1]
>>>
>>> https://www.postgresql.org/message-id/CANu8FixK9P8UD43nv2s%
>> 2Bc-9jHkyy8wPUc_f5K3HcrmJYVPr-wQ%40mail.gmail.com
>>
>>> [1]
>>>
>>
>> --
>>
>> MELVIN DAVIDSON
>> I reserve the right to fantasize.  Whether or not you
>>  wish to share my fantasy is entirely up to you.
>>
>>
>> Links:
>> --
>> [1]
>> https://www.postgresql.org/message-id/CANu8FixK9P8UD43nv2s%
>> 2Bc-9jHkyy8wPUc_f5K3HcrmJYVPr-wQ%40mail.gmail.com
>>
>


*Reinhard,*

*After reviewing things, I note it's possible that you downloaded an
earlier version that had some errors in it and was not as complete.*

*Therefore, I've attached the latest, more complete version of the
function. Please let me know if this solves the problem.*

-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.
-- Function: clone_schema(text, text, boolean)

-- DROP FUNCTION clone_schema(text, text, boolean);

CREATE OR REPLACE FUNCTION clone_schema(
source_schema text,
dest_schema text,
include_recs boolean)
  RETURNS void AS
$BODY$
--  Initial code by Emanuel '3manuek' 
--  Last revision 2015-09-20 by Melvin Davidson
--  This function will clone all sequences, tables, indexes, rules, triggers, 
--  data(optional), views & functions from any existing schema to a new schema
-- SAMPLE CALL:
-- SELECT clone_schema('public', 'new_schema', TRUE);

DECLARE
  src_oid  oid;
  tbl_oid  oid;
  func_oid oid;
  con_oid  oid;
  v_path   text;
  v_func   text;
  v_args   text;
  v_connametext;
  v_rule   text;
  v_trig   text;
  object   text;
  buffer   text;
  srctbl   text;
  default_ text;
  v_column text;
  qry  text;
  dest_qry text;
  v_deftext;
  v_stat   integer;
  seqval   bigint;
  sq_last_valuebigint;
  sq_max_value bigint;
  sq_start_value   bigint;
  sq_increment_by  bigint;
  sq_min_value bigint;
  sq_cache_value   bigint;
  sq_log_cnt   bigint;
  sq_is_called boolean;
  sq_is_cycled boolean;
  sq_cycledchar(10);

BEGIN

-- Check that source_schema exists
  SELECT oid INTO src_oid
FROM pg_namespace
   WHERE nspname = quote_ident(source_schema);
  IF NOT FOUND
THEN 
RAISE NOTICE 'source schema % does not exist!', source_schema;
RETURN ;
  END IF;

  -- Check that dest_schema does not yet exist
  PERFORM nspname 
FROM pg_namespace
   WHERE nspname = quote_ident(dest_schema);
  IF FOUND
THEN 
RAISE