Re: [GENERAL] How to add columns to view with dependencies

2017-04-16 Thread Tom Lane 
Guyren Howe  writes:
> Seems like a simple question, but I’ve never found a good answer to this and 
> similar issues.
> I would think it was safe to let me add columns to a view on which other 
> views depend, but Postgres won’t let me.

> I can imagine ways of sort-of dealing with this. I might maintain a SQL file 
> with views to create in a suitable order, Then I could drop all views, edit 
> the definition of one, then run the file, but this is awfully tedious.

> What is best practice in this situation?

Hm ... all currently-supported versions of Postgres will allow, eg,

regression=# create table t1 (f1 int, f2 int, f3 int);
CREATE TABLE
regression=# create view v1 as select f1 from t1;
CREATE VIEW
regression=# create or replace view v1 as select f1, f2 from t1;
CREATE VIEW
regression=# create view v2 as select * from v1;
CREATE VIEW
regression=# create or replace view v1 as select f1, f2, f3 from t1;
CREATE VIEW

So I think your options are (1) explain what you're really doing,
or (2) update.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How to add columns to view with dependencies

2017-04-16 Thread Justin Pryzby 
On Sun, Apr 16, 2017 at 08:02:54PM -0700, Guyren Howe wrote:
> Seems like a simple question, but I’ve never found a good answer to this and 
> similar issues.
> 
> I would think it was safe to let me add columns to a view on which other 
> views depend, but Postgres won’t let me.
> 
> I can imagine ways of sort-of dealing with this. I might maintain a SQL file 
> with views to create in a suitable order, Then I could drop all views, edit 
> the definition of one, then run the file, but this is awfully tedious.
> 
> What is best practice in this situation?

If you're not re-ordering existing columns, you can use CREATE OR REPLACE VIEW

Justin


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] How to add columns to view with dependencies

2017-04-16 Thread Guyren Howe 
Seems like a simple question, but I’ve never found a good answer to this and 
similar issues.

I would think it was safe to let me add columns to a view on which other views 
depend, but Postgres won’t let me.

I can imagine ways of sort-of dealing with this. I might maintain a SQL file 
with views to create in a suitable order, Then I could drop all views, edit the 
definition of one, then run the file, but this is awfully tedious.

What is best practice in this situation?

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

Re: [GENERAL] Nice to have features: Percentage function

2017-04-16 Thread Adrian Klaver 

On 04/16/2017 02:33 PM, Michael Nolan wrote:

I also have some pre-defined percentage functions, they check the
denominator and return null if it is zero, to avoid 'divide by zero'
errors.


Are they available somewhere?

My previous examples where more proof of concept then complete.



--
Mike Nolan




--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Nice to have features: Percentage function

2017-04-16 Thread Adrian Klaver 

On 04/16/2017 09:37 AM, Melvin Davidson wrote:



On Sun, Apr 16, 2017 at 12:23 PM, Adrian Klaver





*Or, you could just as easily compute inline in SQL:

SELECT datname,
   pg_size_pretty(pg_database_size(datname))as size_pretty,
   pg_database_size(datname) as size,
   (SELECT pg_size_pretty (SUM( pg_database_size(datname))::bigint)
  FROM pg_database)  AS total,
  ((pg_database_size(datname) / (SELECT SUM(
pg_database_size(datname))
   FROM pg_database) ) *
100)::numeric(6,3) AS pct
  FROM pg_database
  ORDER BY datname;*


Yeah, that is doable but I believe the OP is looking for generic 
functions that eliminate the need to write out the math for each query.

A quick and dirty example:

test=# create table percent_test(id int, subtotal numeric, sales_tax 
numeric);

CREATE TABLE
test=# insert into percent_test values (1, 128, 8.7), (2, 90, 8.5), (3, 
256.35, 8.7), (4, 25.50, 8.5);

INSERT 0 4

test=# select id, subtotal, sales_tax as sales_tax_rate, 
percent_of(sales_tax, subtotal)::numeric(7, 2) as tax from percent_test;

 id | subtotal | sales_tax_rate |  tax
+--++---
  1 |  128 |8.7 | 11.14
  2 |   90 |8.5 |  7.65
  3 |   256.35 |8.7 | 22.30
  4 |25.50 |8.5 |  2.17



--
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.



--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Nice to have features: Percentage function

2017-04-16 Thread Michael Nolan 
I also have some pre-defined percentage functions, they check the
denominator and return null if it is zero, to avoid 'divide by zero'
errors.
--
Mike Nolan

On Sun, Apr 16, 2017 at 11:37 AM, Melvin Davidson 
wrote:

>
>
> On Sun, Apr 16, 2017 at 12:23 PM, Adrian Klaver  > wrote:
>
>> On 04/15/2017 10:47 PM, Ron Ben wrote:
>>
>>> Hi,
>>> I'm always finiding myself writing many varations of functions to
>>> calculate percentage.
>>> I think it would be nice if postgresql would have build in functions for
>>> that.
>>> I think the major functionality is something like the 3 ooptions here:
>>> https://percentagecalculator.net/
>>>
>>> It may help to keep code simple and clean and it seem like something
>>> simple to implement.
>>>
>>
>> Plan B, CREATE your own extension. What follows is my first attempt at
>> creating an extension and the functions included are simple placeholders
>> more then anything else:
>>
>> File name: calc_percents--1.0.sql
>>
>> -- complain if script is sourced in psql, rather than via CREATE EXTENSION
>> \echo Use "CREATE EXTENSION calc_percents" to load this file. \quit
>>
>> CREATE OR REPLACE FUNCTION public.percent_of(val1 numeric, val2 numeric)
>>  RETURNS numeric
>>  LANGUAGE sql
>> AS $function$
>>   select (val1 / 100) *  val2;
>> $function$
>> ;
>>
>> CREATE OR REPLACE FUNCTION public.what_percent(val1 numeric, val2 numeric)
>>  RETURNS numeric
>>  LANGUAGE sql
>> AS $function$
>>   SELECT (val1 / val2) * 100;
>> $function$
>> ;
>>
>> CREATE OR REPLACE FUNCTION public.percent_diff(val1 numeric, val2 numeric)
>>  RETURNS numeric
>>  LANGUAGE sql
>> AS $function$
>>   select (val2 - val1) / val1 * 100;
>> $function$
>> ;
>>
>> File name: calc_percents.control
>>
>> # calc_percents extension
>> comment = 'Functions for calculating percentages'
>> default_version = '1.0'
>> relocatable = true
>>
>>
>> Install the above in $SHARE/extension, in my case
>> /usr/local/pgsql/share/extension/
>>
>> Then:
>>
>> test=# create extension calc_percents;
>> CREATE EXTENSION
>> test=# \df percent_of
>>   List of functions
>>  Schema |Name| Result data type |Argument data types |
>> Type
>> ++--+---
>> -+
>>  public | percent_of | numeric  | val1 numeric, val2 numeric |
>> normal
>>
>> test=# select * from round(percent_of(10, 100), 2) ;
>>  round
>> ---
>>  10.00
>>
>>
>> test=# \df percent_diff
>>List of functions
>>  Schema | Name | Result data type |Argument data types |  Type
>> +--+--+-
>> ---+
>>  public | percent_diff | numeric  | val1 numeric, val2 numeric |
>> normal
>>
>>
>> test=# select * from round(percent_diff(100, 109), 2) ;
>>  round
>> ---
>>   9.00
>> (1 row)
>>
>>
>> test=# \df what_percent
>>List of functions
>>  Schema | Name | Result data type |Argument data types |  Type
>> +--+--+-
>> ---+
>>  public | what_percent | numeric  | val1 numeric, val2 numeric |
>> normal
>> (1 row)
>>
>> test=# select * from round(what_percent(10, 109), 2) ;
>>  round
>> ---
>>   9.17
>>
>>
>>
>>> If you think it's a good idea it would be nice if someone can implement
>>> this.
>>>
>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
>
>
>
>
>
>
>
>
>
>
>
> *Or, you could just as easily compute inline in SQL:SELECT datname,
> pg_size_pretty(pg_database_size(datname))as size_pretty,
> pg_database_size(datname) as size,   (SELECT pg_size_pretty (SUM(
> pg_database_size(datname))::bigint)FROM pg_database)  AS
> total,   ((pg_database_size(datname) / (SELECT SUM(
> pg_database_size(datname)) FROM
> pg_database) ) * 100)::numeric(6,3) AS pct  FROM pg_database   ORDER BY
> datname;*
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.
>

Re: [GENERAL] Nice to have features: Percentage function

2017-04-16 Thread Melvin Davidson 
On Sun, Apr 16, 2017 at 12:23 PM, Adrian Klaver 
wrote:

> On 04/15/2017 10:47 PM, Ron Ben wrote:
>
>> Hi,
>> I'm always finiding myself writing many varations of functions to
>> calculate percentage.
>> I think it would be nice if postgresql would have build in functions for
>> that.
>> I think the major functionality is something like the 3 ooptions here:
>> https://percentagecalculator.net/
>>
>> It may help to keep code simple and clean and it seem like something
>> simple to implement.
>>
>
> Plan B, CREATE your own extension. What follows is my first attempt at
> creating an extension and the functions included are simple placeholders
> more then anything else:
>
> File name: calc_percents--1.0.sql
>
> -- complain if script is sourced in psql, rather than via CREATE EXTENSION
> \echo Use "CREATE EXTENSION calc_percents" to load this file. \quit
>
> CREATE OR REPLACE FUNCTION public.percent_of(val1 numeric, val2 numeric)
>  RETURNS numeric
>  LANGUAGE sql
> AS $function$
>   select (val1 / 100) *  val2;
> $function$
> ;
>
> CREATE OR REPLACE FUNCTION public.what_percent(val1 numeric, val2 numeric)
>  RETURNS numeric
>  LANGUAGE sql
> AS $function$
>   SELECT (val1 / val2) * 100;
> $function$
> ;
>
> CREATE OR REPLACE FUNCTION public.percent_diff(val1 numeric, val2 numeric)
>  RETURNS numeric
>  LANGUAGE sql
> AS $function$
>   select (val2 - val1) / val1 * 100;
> $function$
> ;
>
> File name: calc_percents.control
>
> # calc_percents extension
> comment = 'Functions for calculating percentages'
> default_version = '1.0'
> relocatable = true
>
>
> Install the above in $SHARE/extension, in my case
> /usr/local/pgsql/share/extension/
>
> Then:
>
> test=# create extension calc_percents;
> CREATE EXTENSION
> test=# \df percent_of
>   List of functions
>  Schema |Name| Result data type |Argument data types | Type
> ++--+---
> -+
>  public | percent_of | numeric  | val1 numeric, val2 numeric |
> normal
>
> test=# select * from round(percent_of(10, 100), 2) ;
>  round
> ---
>  10.00
>
>
> test=# \df percent_diff
>List of functions
>  Schema | Name | Result data type |Argument data types |  Type
> +--+--+-
> ---+
>  public | percent_diff | numeric  | val1 numeric, val2 numeric |
> normal
>
>
> test=# select * from round(percent_diff(100, 109), 2) ;
>  round
> ---
>   9.00
> (1 row)
>
>
> test=# \df what_percent
>List of functions
>  Schema | Name | Result data type |Argument data types |  Type
> +--+--+-
> ---+
>  public | what_percent | numeric  | val1 numeric, val2 numeric |
> normal
> (1 row)
>
> test=# select * from round(what_percent(10, 109), 2) ;
>  round
> ---
>   9.17
>
>
>
>> If you think it's a good idea it would be nice if someone can implement
>> this.
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>












*Or, you could just as easily compute inline in SQL:SELECT datname,
pg_size_pretty(pg_database_size(datname))as size_pretty,
pg_database_size(datname) as size,   (SELECT pg_size_pretty (SUM(
pg_database_size(datname))::bigint)FROM pg_database)  AS
total,   ((pg_database_size(datname) / (SELECT SUM(
pg_database_size(datname)) FROM
pg_database) ) * 100)::numeric(6,3) AS pct  FROM pg_database   ORDER BY
datname;*

-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Nice to have features: Percentage function

2017-04-16 Thread Adrian Klaver 

On 04/15/2017 10:47 PM, Ron Ben wrote:

Hi,
I'm always finiding myself writing many varations of functions to
calculate percentage.
I think it would be nice if postgresql would have build in functions for
that.
I think the major functionality is something like the 3 ooptions here:
https://percentagecalculator.net/

It may help to keep code simple and clean and it seem like something
simple to implement.


Plan B, CREATE your own extension. What follows is my first attempt at 
creating an extension and the functions included are simple placeholders 
more then anything else:


File name: calc_percents--1.0.sql

-- complain if script is sourced in psql, rather than via CREATE EXTENSION
\echo Use "CREATE EXTENSION calc_percents" to load this file. \quit

CREATE OR REPLACE FUNCTION public.percent_of(val1 numeric, val2 numeric)
 RETURNS numeric
 LANGUAGE sql
AS $function$
  select (val1 / 100) *  val2;
$function$
;

CREATE OR REPLACE FUNCTION public.what_percent(val1 numeric, val2 numeric)
 RETURNS numeric
 LANGUAGE sql
AS $function$
  SELECT (val1 / val2) * 100;
$function$
;

CREATE OR REPLACE FUNCTION public.percent_diff(val1 numeric, val2 numeric)
 RETURNS numeric
 LANGUAGE sql
AS $function$
  select (val2 - val1) / val1 * 100;
$function$
;

File name: calc_percents.control

# calc_percents extension
comment = 'Functions for calculating percentages'
default_version = '1.0'
relocatable = true


Install the above in $SHARE/extension, in my case 
/usr/local/pgsql/share/extension/


Then:

test=# create extension calc_percents;
CREATE EXTENSION
test=# \df percent_of
  List of functions
 Schema |Name| Result data type |Argument data types | 
Type

++--++
 public | percent_of | numeric  | val1 numeric, val2 numeric | 
normal


test=# select * from round(percent_of(10, 100), 2) ;
 round
---
 10.00


test=# \df percent_diff
   List of functions
 Schema | Name | Result data type |Argument data types 
|  Type

+--+--++
 public | percent_diff | numeric  | val1 numeric, val2 numeric 
| normal



test=# select * from round(percent_diff(100, 109), 2) ;
 round
---
  9.00
(1 row)


test=# \df what_percent
   List of functions
 Schema | Name | Result data type |Argument data types 
|  Type

+--+--++
 public | what_percent | numeric  | val1 numeric, val2 numeric 
| normal

(1 row)

test=# select * from round(what_percent(10, 109), 2) ;
 round
---
  9.17




If you think it's a good idea it would be nice if someone can implement
this.



--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Nice to have features: Percentage function

2017-04-16 Thread Adrian Klaver 

On 04/15/2017 10:47 PM, Ron Ben wrote:

Hi,
I'm always finiding myself writing many varations of functions to
calculate percentage.
I think it would be nice if postgresql would have build in functions for
that.


If you have already written the functions, why not just use them?

To make them available across a cluster install them in a template 
database(www.postgresql.org/docs/9.6/static/manage-ag-templatedbs.html)
and create your new databases using that. The default template is the 
template1 database, but you can create your own.



I think the major functionality is something like the 3 ooptions here:
https://percentagecalculator.net/

It may help to keep code simple and clean and it seem like something
simple to implement.

If you think it's a good idea it would be nice if someone can implement
this.



--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general