Re: Diferences between functions criated in pg 8.0.4 and criated in pg 7.4.8

On 5/14/06, Alejandro Michelin Salomon ( Adinet ) <alejmsg(at)adinet(dot)com(dot)uy> wrote:
> Martijn :
>
> OK, y try to explain.
>
> First, y im using phppgAdmin for do this operations, becous my systems are
> all web based.
> My developpement plataform is windows, and this is the reason of use pg
> 8.0.4.
>
> 1) I change :
>
> CREATE OR REPLACE FUNCTION LEFT( sTexto CHARACTER VARYING, nPosFin INTEGER )
> for :
> CREATE OR REPLACE FUNCTION LEFT( CHARACTER VARYING, INTEGER )
>
> Becouse in the online documentation of pg 7.4 does not has other syntax for
> parameters.
>
> 2)
>
> RETURNS "varchar" AS $$ --> Syntax error here.
>
>
> I change the function to
>
> CREATE OR REPLACE FUNCTION LEFT( CHARACTER VARYING, INTEGER )
> RETURNS "varchar"
> LANGUAGE plpgsql
> CALLED ON NULL INPUT
> SECURITY INVOKER
> AS '
> BEGIN
> IF sTexto IS NULL OR nPosFin IS NULL OR nPosFin <= 0 THEN
> RETURN '';
> ELSE
> RETURN SUBSTR( sTexto, 1, nPosFin );
> END IF;
> END;
> ';
>
> At this point i have sourprice. The hosting say does no exist 'plpgsql'
>

once you have the plpgsql created (seems rare, where did you do your
previous tests?), you have solve some other details...

1) parameters have no name so you have to use it with $(number_parameter)
ie:
IF $1 IS NULL OR $2 IS NULL OR $2 <= 0 THEN

or you can use an alias clause to give the parameters a name:

DECLARE
sTexto alias for $1;
nPosFin alias for $2;

2) also this RETURN ''; is wrong because it sees the first ' and think
is the end of the function you have to double type the quote each
time.
ie:
RETURN ''''; -- those are not 2 double quotes but 4 single quotes

--
Atentamente,
Jaime Casanova

"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs and the universe trying
to produce bigger and better idiots.
So far, the universe is winning."
Richard Cook