Hello all
> -----Original Message-----
> From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Karsten Hilbert
> Sent: Dienstag, 17. Mai 2016 09:23
> To: pgsql-general@xxxxxxxxxxxxxx
> Subject: Re: 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 vertical alignment of text to bottom:
select pg_logo(true,ARRAY['Swiss PGDay 2016','24.06.2016 - HSR
Rapperswil','Switzerland','http://www.pgday.ch'],'right','left','bottom');
pg_logo
-------------------------------------------------------
+---------------------------------------------------+
| ____ ______ ___ |
| / )/ \/ \ |
| ( / __ _\ ) |
| \ (/ o) ( o) ) |
| \_ (_ ) \ ) _/ |
| \ /\_/ \)/ Swiss PGDay 2016 |
| \/ <//| |\\> 24.06.2016 - HSR Rapperswil |
| _| | Switzerland |
| \|_/ http://www.pgday.ch |
| |
+---------------------------------------------------+
(12 rows)
And all other combinations, e.g. Put text below the pic and left aligned without frame:
select pg_logo(false,ARRAY['Swiss PGDay 2016','24.06.2016 - HSR Rapperswil','Switzerland','http://www.pgday.ch'],'bottom','left');
pg_logo
-----------------------------
____ ______ ___
/ )/ \/ \
( / __ _\ )
\ (/ o) ( o) )
\_ (_ ) \ ) _/
\ /\_/ \)/
\/ <//| |\\>
_| |
\|_/
Swiss PGDay 2016
24.06.2016 - HSR Rapperswil
Switzerland
http://www.pgday.ch
(14 rows)
>
> 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@xxxxxxxxxxxxxx)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
------------------------------------------------------------------------------
-- 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))||
' |'
ELSE v_pic_line||
repeat(' ',v_tot_width-length(v_pic_line))
END;
WHEN p_align = 'right' THEN
RETURN QUERY SELECT CASE
WHEN p_frame THEN '| '||
repeat(' ',v_tot_width-length(v_pic_line))||
v_pic_line||
' |'
ELSE repeat(' ',v_tot_width-length(v_pic_line))||
v_pic_line
END;
ELSE
RETURN QUERY SELECT CASE
WHEN p_frame THEN '| '||
repeat(' ',(v_tot_width-length(v_pic_line))/2)||
v_pic_line||
repeat(' ',round((v_tot_width-length(v_pic_line)::REAL)/2::REAL)::INTEGER)||
' |'
ELSE repeat(' ',(v_tot_width-length(v_pic_line))/2)||
v_pic_line||
repeat(' ',round((v_tot_width-length(v_pic_line)::REAL)/2::REAL)::INTEGER)
END;
END CASE;
ELSE
-- This part only applies if there is a text that must be placed to the right of the pic.
IF v_text_height > 0 THEN
v_line_count := v_line_count + 1;
CASE WHEN p_align = 'left' THEN
RETURN QUERY SELECT CASE
WHEN p_frame THEN '| '||
v_pic_line||' '||
coalesce(p_text[v_line_count],'')||
repeat(' ',v_tot_width-length(v_pic_line||
coalesce(p_text[v_line_count],''))-1)||
' |'
ELSE v_pic_line||' '||
coalesce(p_text[v_line_count],'')||
repeat(' ',v_tot_width-length(v_pic_line||
coalesce(p_text[v_line_count],''))-1)
END;
WHEN p_align = 'right' THEN
RETURN QUERY SELECT CASE
WHEN p_frame THEN '| '||
v_pic_line||
repeat(' ',v_tot_width-length(v_pic_line||
coalesce(p_text[v_line_count],'')))||
coalesce(p_text[v_line_count],'')||
' |'
ELSE v_pic_line||
repeat(' ',v_tot_width-length(v_pic_line||
coalesce(p_text[v_line_count],'')))||
coalesce(p_text[v_line_count],'')
END;
ELSE
RETURN QUERY SELECT CASE
WHEN p_frame THEN '| '||
v_pic_line||' '||
repeat(' ',(v_max_text_width-(length(coalesce(p_text[v_line_count],''))))/2)||
coalesce(p_text[v_line_count],'')||
repeat(' ',round((v_max_text_width::REAL-(length(coalesce(p_text[v_line_count],''))::REAL))/2::REAL)::INTEGER)||
' |'
ELSE v_pic_line||' '||
repeat(' ',(v_max_text_width-(length(coalesce(p_text[v_line_count],''))))/2)||
coalesce(p_text[v_line_count],'')||
repeat(' ',round((v_max_text_width::REAL-(length(coalesce(p_text[v_line_count],''))::REAL))/2::REAL)::INTEGER)
END;
END CASE;
END IF;
END CASE;
END LOOP;
-- This part only applies if the there is a text that must be place below the pic.
IF p_position = 'bottom' AND v_text_height > 0 THEN
RETURN QUERY SELECT CASE
WHEN p_frame THEN '| '||repeat(' ',v_tot_width)||' |'
ELSE repeat(' ',v_tot_width)
END;
FOREACH v_pic_line IN ARRAY p_text
LOOP
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))||
' |'
ELSE v_pic_line||
repeat(' ',v_tot_width-length(v_pic_line))
END;
WHEN p_align = 'right' THEN
RETURN QUERY SELECT CASE
WHEN p_frame THEN '| '||
repeat(' ',v_tot_width-length(v_pic_line))||
v_pic_line||
' |'
ELSE repeat(' ',v_tot_width-length(v_pic_line))||
v_pic_line
END;
ELSE
RETURN QUERY SELECT CASE
WHEN p_frame THEN '| '||
repeat(' ',(v_tot_width-length(v_pic_line))/2)||
v_pic_line||
repeat(' ',round((v_tot_width-length(v_pic_line)::REAL)/2::REAL)::INTEGER)||
' |'
ELSE repeat(' ',(v_tot_width-length(v_pic_line))/2)||
v_pic_line||
repeat(' ',round((v_tot_width-length(v_pic_line)::REAL)/2::REAL)::INTEGER)
END;
END CASE;
END LOOP;
END IF;
IF p_frame THEN
RETURN QUERY SELECT '| '||repeat(' ',v_tot_width)||' |';
RETURN QUERY SELECT '+-'||repeat('-',v_tot_width)||'-+';
END IF;
RETURN;
END;
$$ LANGUAGE plpgsql;
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
