Re: PL/PGSQL syntax manual - Mailing list pgsql-novice

From ERIC Lawson - x52010
Subject Re: PL/PGSQL syntax manual
Date
Msg-id Pine.GSO.4.10.10009141547210.11946-100000@gandalf.bioeng.washington.edu
Whole thread Raw
In response to PL/PGSQL syntax manual  ("Chau, Artemis" <artemis.chau@intel.com>)
List pgsql-novice
Artemis,

I've been looking for a manual also, so far without substantial success.
If you turn one up, please let me know.  So far, my best sources of
information have been 1) the documentation online (this is short of
syntactical information and practical examples); 2) the online chapter of
the Momjian book at
http://www.postgresql.org/docs/aw_pgsql_book/node202.html; 3) replies from
postgresql listeners with expertise on the novice and general postgresql
mailing lists.

I wrote a function that takes text and returns the proper value for a date
field (in 6.5.3); with extrapolation and experimentation, it might work
for your purpose:

create function dateret(text)
    returns date
    as 'begin
        if length($1)=0
            then return NULL;
            else return $1;
        end if;
    end;'
    language 'plpgsql';


Extrapolated (without experimentation):

create function textret(integer)
    returns text
    as 'begin
        if length($1)=0
            then return '';
            else return '$1::text' . '[text to concatenate onto integer]';
        end if;
    end;'
    language 'plpgsql';

No idea if that will work for you, but I suggest that by using an explicit
cast and the concatenation operator, plpgsql is unnecessary:  an sql
language function would do it as well.

Note that an sql language function wouldn't work for my dateret function
above because a) I'm getting the data from a CGI interface, and b)
stuffing empty strings into a date field, while it appeared to succeed
(remember, I'm using 6.5.3), resulted in the table becoming corrupted
(actually, just the particular tuple, but the net result was an
inaccessible table).

Also, of course, my lack of expertise is noticable; some other solution
with less complexity might've worked as well as the dateret function to
achieve my aim.

regards,
Eric

James Eric Lawson
Research Publications Editor III
National Simulation Resource

eric@bioeng.washington.edu

On Thu, 14 Sep 2000, Chau, Artemis wrote:

> I need to create a function that requires to convert a integer into a string
> and concatenate it with another string.  Does anyone know how to write the
> code in PL/PGSQL or where to find a manual for the language?
> Thanks!
>
> -- Artemis
>
>



pgsql-novice by date:

Previous
From: "Chau, Artemis"
Date:
Subject: PL/PGSQL syntax manual
Next
From: Tyler Bye
Date:
Subject: dynamic functions in plpgsql