Elephant

From PostgreSQL wiki
Jump to navigationJump to search

Snippets

Elephant

Works with PostgreSQL

9.0+

Written in

plpgsql

Depends on

Nothing


Based on this mailing thread: [1].

The original idea was to have a simple way to create the PostgreSQL elephant logo with some minimal text formatting as an ASCII image for mail signatures.

Instead of the originally intended name pg_logo I finally opted for a more or less neutral elephant.

CREATE OR REPLACE FUNCTION public.elephant(
  p_frame boolean DEFAULT false,
  p_text text[] DEFAULT NULL::text[],
  p_position text DEFAULT 'bottom'::text,
  p_align text DEFAULT 'center'::text,
  p_valign text DEFAULT 'center'::text
)
 RETURNS SETOF text
 IMMUTABLE
 LANGUAGE plpgsql
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::NUMERIC-length(v_pic_line)::NUMERIC)/2::NUMERIC)::INTEGER)||
                                                ' |'
                                   ELSE repeat(' ',(v_tot_width-length(v_pic_line))/2)||
                                        v_pic_line||
                                        repeat(' ',round((v_tot_width::NUMERIC-length(v_pic_line)::NUMERIC)/2::NUMERIC)::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::NUMERIC-(length(coalesce(p_text[v_line_count],''))::NUMERIC))/2::NUMERIC)::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::NUMERIC-(length(coalesce(p_text[v_line_count],''))::NUMERIC))/2::NUMERIC)::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::NUMERIC-length(v_pic_line)::NUMERIC)/2::NUMERIC)::INTEGER)||
                                                     ' |'
                                   ELSE repeat(' ',(v_tot_width-length(v_pic_line))/2)||
                                        v_pic_line||
                                        repeat(' ',round((v_tot_width::NUMERIC-length(v_pic_line)::NUMERIC)/2::NUMERIC)::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;
$$;

COMMENT ON FUNCTION public.elephant() IS $$Show PostgreSQL elephant as ASCII image on screen
Parameters:
p_frame boolean: Show picture in a frame (DEFAULT false)
p_text text[]: Show a text with the image (one line per array element, DEFAULT NULL)
p_position text: Position of the text to the image (bottom, top, left, right, DEFAULT bottom)
p_align text: Align horizontally (bottom, top, center, DEFAULT center)
p_valign text Align vertically (bottom, top, center, DEFAULT center)
$$;

Usage

Only default values.

SELECT * FROM public.elephant();

   ____  ______  ___
  /    )/      \/   \
 (     / __    _\    )
  \    (/ o)  ( o)   )
   \_  (_  )   \ ) _/
     \  /\_/    \)/
      \/ <//|  |\\>
           _|  |
           \|_/

Adding frame and some text.

SELECT * FROM public.elephant(
  TRUE,
  ARRAY['30 years PostgreSQL','1996 - 2026'],
  'bottom'
);

 +-----------------------+
 |   ____  ______  ___   |
 |  /    )/      \/   \  |
 | (     / __    _\    ) |
 |  \    (/ o)  ( o)   ) |
 |   \_  (_  )   \ ) _/  |
 |     \  /\_/    \)/    |
 |      \/ <//|  |\\>    |
 |           _|  |       |
 |           \|_/        |
 |                       |
 |  30 years PostgreSQL  |
 |      1996 - 2026      |
 |                       |
 +-----------------------+