Re: [GENERAL] Ascii Elephant for text based protocols - Final

[Karsten Hilbert]

> On Tue, May 17, 2016 at 8:25 AM, Victor Yegorov  wrote:
> > I had a bit of fun with this SQL version and came up with this query:
> >
> > WITH src(s) AS (
> >   VALUES
> > ('729472967293732174412176b12173b17111752171927491b1744171b17411217181417211718141734172b191721191724173b1714171912175b17221b1912174b1412178b121715122a172a1b2317d91a172a17f71b1a1912177')
> > ), str AS (
> >   SELECT string_agg(repeat(translate(substr(s, p, 1), '123456789ab', '(/>)<+
> > o_|\'), ('x'||lpad(substr(s, p+1, 1), 8, '0'))::bit(32)::int), '') line
> > FROM src, generate_series(1, 182, 2) p
> > )
> > SELECT substr(line, p, 21) slon FROM str, generate_series(1, 189, 21) p;

I would nominate this to be called

 select pg_logo_obfuscated();

Karsten


-- 
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] Ascii Elephant for text based protocols - Final

[Michael Paquier]

On Tue, May 17, 2016 at 8:25 AM, Victor Yegorov  wrote:
> I had a bit of fun with this SQL version and came up with this query:
>
> WITH src(s) AS (
>   VALUES
> ('729472967293732174412176b12173b17111752171927491b1744171b17411217181417211718141734172b191721191724173b1714171912175b17221b1912174b1412178b121715122a172a1b2317d91a172a17f71b1a1912177')
> ), str AS (
>   SELECT string_agg(repeat(translate(substr(s, p, 1), '123456789ab', '(/>)<+
> o_|\'), ('x'||lpad(substr(s, p+1, 1), 8, '0'))::bit(32)::int), '') line
> FROM src, generate_series(1, 182, 2) p
> )
> SELECT substr(line, p, 21) slon FROM str, generate_series(1, 189, 21) p;

You just made my day.
-- 
Michael


-- 
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] Ascii Elephant for text based protocols - Final

[Jan de Visser]

On Tuesday, May 17, 2016 9:41:51 AM EDT Bryan Nuse wrote:
> On 05/17/2016 08:25 AM, Victor Yegorov wrote:
> > I had a bit of fun with this SQL version and came up with this query:
> > 
> > WITH src(s) AS (
> > 
> >   VALUES
> > 
> > ('729472967293732174412176b12173b17111752171927491b1744171b174112171814172
> > 11718141734172b191721191724173b1714171912175b17221b1912174b1412178b1217151
> > 22a172a1b2317d91a172a17f71b1a1912177') ), str AS (
> > 
> >   SELECT string_agg(repeat(translate(substr(s, p, 1), '123456789ab',
> > 
> > '(/>)<+ o_|\'), ('x'||lpad(substr(s, p+1, 1), 8, '0'))::bit(32)::int),
> > '') line
> > 
> > FROM src, generate_series(1, 182, 2) p
> > 
> > )
> > SELECT substr(line, p, 21) slon FROM str, generate_series(1, 189, 21) p;
> 
> I wonder if --under some fonts especially-- that elephant's eyes look a
> little... aggressive?
> Perhaps the following is too cute, however:
> 
>   __  ___
>/)/  \/   \
>   ( / ___\)
>\(/ p)  ( g)   )
> \_  (_  )   \ ) _/
>   \  /\_/\)/
>\/ 
> _|  |
> \|_/

Some people have WAY too much time on their hands.




-- 
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] Ascii Elephant for text based protocols - Final

[Bryan Nuse]

On 05/17/2016 08:25 AM, Victor Yegorov wrote:


I had a bit of fun with this SQL version and came up with this query:

WITH src(s) AS (
  VALUES 
('729472967293732174412176b12173b17111752171927491b1744171b17411217181417211718141734172b191721191724173b1714171912175b17221b1912174b1412178b121715122a172a1b2317d91a172a17f71b1a1912177')

), str AS (
  SELECT string_agg(repeat(translate(substr(s, p, 1), '123456789ab', 
'(/>)<+ o_|\'), ('x'||lpad(substr(s, p+1, 1), 8, '0'))::bit(32)::int), 
'') line

FROM src, generate_series(1, 182, 2) p
)
SELECT substr(line, p, 21) slon FROM str, generate_series(1, 189, 21) p;




I wonder if --under some fonts especially-- that elephant's eyes look a 
little... aggressive?

Perhaps the following is too cute, however:

     __  ___
  /)/  \/   \
 ( / ___\)
  \(/ p)  ( g)   )
   \_  (_  )   \ ) _/
 \  /\_/\)/
  \/ 
   _|  |
   \|_/

--
Postdoctoral Researcher
Georgia Cooperative Fish & Wildlife Research Unit
University of Georgia
Athens, GA 30606-2152

Re: [GENERAL] Ascii Elephant for text based protocols - Final function proposal

[Charles Clavadetscher]

Hello again

> -Original Message-
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Charles
> Clavadetscher
> Sent: Dienstag, 17. Mai 2016 14:50
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Ascii Elephant for text based protocols - Final 
> function proposal
> 
> Hello all
> 
> > -Original Message-
> > From: pgsql-general-ow...@postgresql.org
> > [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Karsten
> > Hilbert
> > Sent: Dienstag, 17. Mai 2016 09:23
> > To: pgsql-general@postgresql.org
> > Subject: Re: [GENERAL] Ascii Elephant for text based protocols - Final
> >
> > On Tue, May 17, 2016 at 06:58:14AM +0200, Charles Clavadetscher wrote:
> >
> > > A question to the naming. I find pg_logo() also a good name, but is
> > > the prefix pg_* not reserved for system functions? Of course I could
> > > use the name I want, but was wondering if there is a policy or a
> > > best practice in this area.
> >
> > pg_logo would only be suitable if it got blessing from "higher up".
> 
> Well. This question will be addressed when the body of the function is 
> complete. Now I have a proposal which is in
> the attachment.

In my function there was a problem with casting of values to real instead of 
numeric to compute the number of spaces required on the
right side of a string to be centered. In some specific string lengths the 
rounding was not correct.
Sorry. This should now be ok.

In the attachment the corrected version.

Bye
Charles

--
-- Display the PostgreSQL logo as ASCII art with
-- an optional frame and optional text.
--
CREATE OR REPLACE FUNCTION pg_logo(p_frame BOOLEAN DEFAULT false,
   p_text TEXT[] DEFAULT NULL,
   p_position TEXT DEFAULT 'bottom',
   p_align TEXT DEFAULT 'center',
   p_valign TEXT DEFAULT 'center')
RETURNS SETOF TEXT
AS $$
DECLARE
  v_pic TEXT[] := ARRAY[
  '    __  ___  ',
  ' /)/  \/   \ ',
  '( / ___\)',
  ' \(/ o)  ( o)   )',
  '  \_  (_  )   \ ) _/ ',
  '\  /\_/\)/   ',
  ' \/',
  '  _|  |  ',
  '  \|_/   '];
  v_pic_width INTEGER := coalesce((SELECT max(length(x))
   FROM unnest(v_pic) x),0);
  v_pic_height INTEGER := array_length(v_pic,1);
  -- Get the longest text available or zero if none.
  v_max_text_width INTEGER := coalesce((SELECT max(length(x))
FROM unnest(p_text) x),0);
  v_text_height INTEGER := coalesce(array_length(p_text,1),0);
  -- Compute total width including a space if text is on the right.
  -- This value does not include the frame (if requested).
  v_tot_width INTEGER := CASE WHEN p_position = 'bottom' THEN
greatest(v_max_text_width,v_pic_width)
  ELSE v_pic_width+v_max_text_width+1
 END;
  v_pic_line TEXT;
  v_line_count INTEGER; -- Used for vertical alignment of text
BEGIN
  IF v_text_height > 8 THEN
  END IF;
  -- Check positioning and alignments. Fall back to default if
  -- values are not allowed.
  IF lower(coalesce(p_position,'')) NOT IN ('bottom','right') THEN
p_position := 'bottom';
p_position := lower(p_position);
  END IF;
  IF lower(coalesce(p_align,'')) NOT IN ('left','center','right') THEN
p_align := 'center';
p_align := lower(p_align);
  END IF;
  IF lower(coalesce(p_valign,'')) NOT IN ('top','center','bottom') THEN
p_valign := 'center';
p_valign := lower(p_position);
  END IF;
  -- Add top frame line.
  IF p_frame THEN
RETURN QUERY SELECT '+-'||repeat('-',v_tot_width)||'-+';
  END IF;
  -- Reset counter for vertical alignment of right positioned text.
  CASE WHEN p_valign = 'top' THEN v_line_count := -1; -- It looks better like 
this.
   WHEN p_valign = 'bottom' THEN v_line_count := v_text_height-v_pic_height;
   ELSE v_line_count := (v_text_height-v_pic_height)/2;
   IF v_line_count = 0 THEN v_line_count := -1; -- Correct for case when 
number
   END IF;  -- of text lines is 8
  END CASE;
  FOREACH v_pic_line IN ARRAY v_pic
  LOOP
CASE WHEN p_position = 'bottom' THEN
  CASE WHEN p_align = 'left' THEN
 RETURN QUERY SELECT CASE
   WHEN p_frame THEN '| '||
 v_pic_line||
 repeat(' 
',v_tot_width-length(v_pic_line))||
  

Re: [GENERAL] Ascii Elephant for text based protocols - Final function proposal

[Charles Clavadetscher]

Hello all

> -Original Message-
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Karsten Hilbert
> Sent: Dienstag, 17. Mai 2016 09:23
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Ascii Elephant for text based protocols - Final
> 
> On Tue, May 17, 2016 at 06:58:14AM +0200, Charles Clavadetscher wrote:
> 
> > A question to the naming. I find pg_logo() also a good name, but is
> > the prefix pg_* not reserved for system functions? Of course I could
> > use the name I want, but was wondering if there is a policy or a best
> > practice in this area.
> 
> pg_logo would only be suitable if it got blessing from "higher up".

Well. This question will be addressed when the body of the function is 
complete. Now I have a proposal which is in the attachment.

The function accepts a set of arguments, all having default values:

p_frame BOOLEAN DEFAULT false: values true or false.
p_text TEXT[] DEFAULT NULL: Free text as array of text. Each element of the 
array is a line of text. Maximum of 8 lines. What is
above is ignored.
p_position TEXT DEFAULT 'bottom': position of the text in relation to the pic. 
bottom or right.
p_align TEXT DEFAULT 'center': Alignment of pic AND text, when p_position is 
bottom, only of the text otherwise. Values: left,
center, right.
p_valign TEXT DEFAULT 'center': Vertical alignement of the text. Only applies 
if p_position is right. Values: top, center, bottom.

Below I add a set of sample outputs. Feel free to test it and all feedbacks are 
as usual very much appreciated.

Regards,
Charles

Sample outputs:

No arguments (all defaults):
select pg_logo();
pg_logo
---
     __  ___
  /)/  \/   \
 ( / ___\)
  \(/ o)  ( o)   )
   \_  (_  )   \ ) _/
 \  /\_/\)/
  \/ 
   _|  |
   \|_/
(9 rows)

Add frame:
select pg_logo(true);
  pg_logo
---
 +---+
 |     __  ___   |
 |  /)/  \/   \  |
 | ( / ___\) |
 |  \(/ o)  ( o)   ) |
 |   \_  (_  )   \ ) _/  |
 | \  /\_/\)/|
 |  \/ |
 |   _|  |   |
 |   \|_/|
 |   |
 +---+
(12 rows)

Add text using default positioning and alignments:
select pg_logo(true,ARRAY['Swiss PGDay 2016','24.06.2016 - HSR 
Rapperswil','Switzerland','http://www.pgday.ch']);
 pg_logo
-
 +-+
 |    __  ___  |
 | /)/  \/   \ |
 |( / ___\)|
 | \(/ o)  ( o)   )|
 |  \_  (_  )   \ ) _/ |
 |\  /\_/\)/   |
 | \/|
 |  _|  |  |
 |  \|_/   |
 | |
 |  Swiss PGDay 2016   |
 | 24.06.2016 - HSR Rapperswil |
 | Switzerland |
 | http://www.pgday.ch |
 | |
 +-+
(17 rows)

Position text on the right hand side of the pic:
select pg_logo(true,ARRAY['Swiss PGDay 2016','24.06.2016 - HSR 
Rapperswil','Switzerland','http://www.pgday.ch'],'right');
pg_logo
---
 +---+
 |     __  ___   |
 |  /)/  \/   \  |
 | ( / ___\)  Swiss PGDay 2016   |
 |  \(/ o)  ( o)   ) 24.06.2016 - HSR Rapperswil |
 |   \_  (_  )   \ ) _/  Switzerland |
 | \  /\_/\)/http://www.pgday.ch |
 |  \/ |
 |   _|  |   |
 |   \|_/|
 |   |
 +---+
(12 rows)

Change text alignment to left:
select pg_logo(true,ARRAY['Swiss PGDay 2016','24.06.2016 - HSR 
Rapperswil','Switzerland','http://www.pgday.ch'],'right','left');
pg_logo
---
 +---+
 |     __  ___   |
 |  /)/  \/   \  |
 | ( / ___\) Swiss PGDay 2016|
 |  \(/ o)  ( o)   ) 24.06.2016 - HSR Rapperswil |
 |   \_  (_  )   \ ) _/  Switzerland |
 | \  /\_/\)/http://www.pgday.ch |
 |  \/ |
 |   _|  |   |
 |   \|_/|
 |   |
 +---+
(12 rows)

Change ver

Re: [GENERAL] Ascii Elephant for text based protocols - Final

[Victor Yegorov]

2016-05-16 14:47 GMT+03:00 Charles Clavadetscher :

> SQL version by Melvin Davidson:
>
> CREATE TABLE elephant
> (row_num integer NOT NULL,
>  row_dat varchar(30) NOT NULL,
>  CONSTRAINT elephant_pk PRIMARY KEY (row_num)
> );
>
> INSERT INTO elephant
> (row_num, row_dat)
> VALUES
> ( 1,'++'),
> ( 2,'|     __  ___|'),
> ( 3,'|  /)/  \/   \   |'),
> ( 4,'| ( / ___\)  |'),
> ( 5,'|  \(/ o)  ( o)   )  |'),
> ( 6,'|   \_  (_  )   \ ) _/   |'),
> ( 7,'| \  /\_/\)/ |'),
> ( 8,'|  \/  |'),
> ( 9,'|   _|  ||'),
> (10,'|   \|_/ |'),
> (11,'||'),
> (12,'|  PostgreSQL 1996-2016  |'),
> (13,'|  20 Years of success   |'),
> (14,'++');
>
> SELECT row_dat FROM elephant ORDER BY row_num;
>


I had a bit of fun with this SQL version and came up with this query:

WITH src(s) AS (
  VALUES
('729472967293732174412176b12173b17111752171927491b1744171b17411217181417211718141734172b191721191724173b1714171912175b17221b1912174b1412178b121715122a172a1b2317d91a172a17f71b1a1912177')
), str AS (
  SELECT string_agg(repeat(translate(substr(s, p, 1), '123456789ab',
'(/>)<+ o_|\'), ('x'||lpad(substr(s, p+1, 1), 8, '0'))::bit(32)::int), '')
line
FROM src, generate_series(1, 182, 2) p
)
SELECT substr(line, p, 21) slon FROM str, generate_series(1, 189, 21) p;


-- 
Victor Y. Yegorov

Re: [GENERAL] Ascii Elephant for text based protocols - Final

[Karsten Hilbert]

On Tue, May 17, 2016 at 06:58:14AM +0200, Charles Clavadetscher wrote:

> A question to the naming. I find pg_logo() also a good name, but is the
> prefix pg_* not reserved for system functions? Of course I could use the
> name I want, but was wondering if there is a policy or a best practice in
> this area.

pg_logo would only be suitable if it got blessing from "higher up".

Karsten
-- 
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346


-- 
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] Ascii Elephant for text based protocols - Final

[Charles Clavadetscher]

Hello

On 05/16/2016 10:50 PM, Michael Paquier wrote:

On Tue, May 17, 2016 at 5:13 AM, Karsten Hilbert
 wrote:

 select pg_logo();

seems like a good idea to me :-)


If you propose a patch with a function that returns a setof text, I am
sure it would get some attention. Though I think that you should
remove the mention of the 20th anniversary.



Thank you for the feedbacks.

Good idea. I will write a function. The most basic would be:

CREATE OR REPLACE FUNCTION pg_logo()
RETURNS SETOF TEXT
AS $$
BEGIN
  RETURN QUERY SELECT '    __  ___  '::TEXT;
  RETURN QUERY SELECT ' /)/  \/   \ '::TEXT;
  RETURN QUERY SELECT '( / ___\)'::TEXT;
  RETURN QUERY SELECT ' \(/ o)  ( o)   )'::TEXT;
  RETURN QUERY SELECT '  \_  (_  )   \ ) _/ '::TEXT;
  RETURN QUERY SELECT '\  /\_/\)/   '::TEXT;
  RETURN QUERY SELECT ' \/'::TEXT;
  RETURN QUERY SELECT '  _|  |  '::TEXT;
  RETURN QUERY SELECT '  \|_/   '::TEXT;
  RETURN;
END;
$$ LANGUAGE plpgsql;

charles@charles.[local]=# select pg_logo();
pg_logo
---
     __  ___
  /)/  \/   \
 ( / ___\)
  \(/ o)  ( o)   )
   \_  (_  )   \ ) _/
 \  /\_/\)/
  \/ 
   _|  |
   \|_/
(9 rows)

But I'd like to add arguments so that it is possible to add a frame and 
a text as TEXT[]. Additional arguments for the text would be the 
position (bottom or right) and the alignments (top, bottom, left, right, 
center). All arguments should have default values so that calling the 
function without arguments delivers the logo as in the above basic function.


A question to the naming. I find pg_logo() also a good name, but is the 
prefix pg_* not reserved for system functions? Of course I could use the 
name I want, but was wondering if there is a policy or a best practice 
in this area.


And what schema would be appropriate? I could also create the function 
without defining a schema and let users decide where they want to put it.


An alternative for the name could be logo(), in analogy to version().

Bye
Charles


--
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] Ascii Elephant for text based protocols - Final

[Michael Paquier]

On Tue, May 17, 2016 at 5:13 AM, Karsten Hilbert
 wrote:
> select pg_logo();
>
> seems like a good idea to me :-)

If you propose a patch with a function that returns a setof text, I am
sure it would get some attention. Though I think that you should
remove the mention of the 20th anniversary.
-- 
Michael


-- 
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] Ascii Elephant for text based protocols - Final

[Karsten Hilbert]

select pg_logo();

seems like a good idea to me :-)

Karsten

> SQL version by Melvin Davidson:
> 
> CREATE TABLE elephant
> (row_num integer NOT NULL,
>  row_dat varchar(30) NOT NULL,
>  CONSTRAINT elephant_pk PRIMARY KEY (row_num)
> );
> 
> INSERT INTO elephant
> (row_num, row_dat)
> VALUES
> ( 1,'++'),
> ( 2,'|     __  ___|'),
> ( 3,'|  /)/  \/   \   |'),
> ( 4,'| ( / ___\)  |'),
> ( 5,'|  \(/ o)  ( o)   )  |'),
> ( 6,'|   \_  (_  )   \ ) _/   |'),
> ( 7,'| \  /\_/\)/ |'),
> ( 8,'|  \/  |'),
> ( 9,'|   _|  ||'),
> (10,'|   \|_/ |'),
> (11,'||'),
> (12,'|  PostgreSQL 1996-2016  |'),
> (13,'|  20 Years of success   |'),
> (14,'++');
> 
> SELECT row_dat FROM elephant ORDER BY row_num;

-- 
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346


-- 
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] Ascii Elephant for text based protocols - Final

[Oleg Bartunov]

On Mon, May 16, 2016 at 2:47 PM, Charles Clavadetscher
 wrote:
> SELECT row_dat FROM elephant ORDER BY row_num;


Very good ! Thanks !


-- 
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] Ascii Elephant for text based protocols - Final

[Charles Clavadetscher]

Hi all

OK. I must come to an end with this task.

This is what I find a decent final version and I plan to use it in my 
SwissPUG signature until the end of the year.


++
|     __  ___|
|  /)/  \/   \   |
| ( / ___\)  |
|  \(/ o)  ( o)   )  |
|   \_  (_  )   \ ) _/   |
| \  /\_/\)/ |
|  \/  |
|   _|  ||
|   \|_/ |
||
|  PostgreSQL 1996-2016  |
|  20 years of success   |
++

It suggests that the trunk is bent behind, like while feeding and I 
assume that associations with phallic symbols doesn't apply anymore.


Thank you to all that provided feedbacks, suggestions and compliments.

SQL version by Melvin Davidson:

CREATE TABLE elephant
(row_num integer NOT NULL,
 row_dat varchar(30) NOT NULL,
 CONSTRAINT elephant_pk PRIMARY KEY (row_num)
);

INSERT INTO elephant
(row_num, row_dat)
VALUES
( 1,'++'),
( 2,'|     __  ___|'),
( 3,'|  /)/  \/   \   |'),
( 4,'| ( / ___\)  |'),
( 5,'|  \(/ o)  ( o)   )  |'),
( 6,'|   \_  (_  )   \ ) _/   |'),
( 7,'| \  /\_/\)/ |'),
( 8,'|  \/  |'),
( 9,'|   _|  ||'),
(10,'|   \|_/ |'),
(11,'||'),
(12,'|  PostgreSQL 1996-2016  |'),
(13,'|  20 Years of success   |'),
(14,'++');

SELECT row_dat FROM elephant ORDER BY row_num;

Regards
Charles


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