Обсуждение: Diferences between functions criated in pg 8.0.4 and criated in pg 7.4.8

Hi :

I criated some function in my pg 8.0.4, they function as spected.

But when i try tu criate this functions in my hosting that has pg 7.4.8, i
can not do.
I read documentation an i see some disferences.

This is one of original function that i try to criate :

CREATE OR REPLACE FUNCTION LEFT( sTexto CHARACTER VARYING, nPosFin INTEGER )
RETURNS "varchar" AS $$

BEGIN
    IF sTexto IS NULL OR nPosFin IS NULL OR nPosFin <= 0 THEN
        RETURN '';
    ELSE
        RETURN SUBSTR( sTexto, 1, nPosFin );
    END IF;
END;
$$ LANGUAGE plpgsql
CALLED ON NULL INPUT
SECURITY INVOKER;

Left is not part of postgresql functions. I am migrating a system does runs
in mysql to postgresql.
To make easy i create some mysql functions in pg/plsql.

How i need change, to get this function runing in pg 7.4.8 ?

Thanks in advnace.

Alejandro Michelin Salomon
Porto Alegre
Brasil

--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.392 / Virus Database: 268.5.6/339 - Release Date: 14/5/2006




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.392 / Virus Database: 268.5.6/339 - Release Date: 14/5/2006

On Sun, May 14, 2006 at 10:10:44AM -0300, Alejandro Michelin Salomon ( Adinet ) wrote:
> Hi :
>
> I criated some function in my pg 8.0.4, they function as spected.
>
> But when i try tu criate this functions in my hosting that has pg 7.4.8, i
> can not do.
> I read documentation an i see some disferences.

<snip>

> How i need change, to get this function runing in pg 7.4.8 ?

Well, since you havn't told us the error message it's hard to tell the
problem. At a guess though, 7.4 doesn't understand dollar-quoting.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Alejandro Michelin Salomon ( Adinet ) <alejmsg@adinet.com.uy> schrieb:
> I criated some function in my pg 8.0.4, they function as spected.
>
> But when i try tu criate this functions in my hosting that has pg 7.4.8, i
> can not do.
> I read documentation an i see some disferences.
>
> This is one of original function that i try to criate :
>
> CREATE OR REPLACE FUNCTION LEFT( sTexto CHARACTER VARYING, nPosFin INTEGER )
> RETURNS "varchar" AS $$
                       ^^
this works only since 8.0 or 8.1, i'm not sure, but not in 7.x.

You need "create or replace ... as '" and within the funktion '' for
single '. Please read the documentation for 7.x.


HTH, Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."    (unknow)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

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'

This last syntax has error, but i can't find.

Can you help me ?

Alejandro MSG <alejmsg@adinet.com.uy>
Porto Alegre
Brasil


-->-----Mensagem original-----
-->De: pgsql-general-owner@postgresql.org
-->[mailto:pgsql-general-owner@postgresql.org] Em nome de
-->Martijn van Oosterhout
-->Enviada em: domingo, 14 de maio de 2006 10:38
-->Para: Alejandro Michelin Salomon ( Adinet )
-->Cc: Pgsql-General
-->Assunto: Re: [GENERAL] Diferences between functions criated
-->in pg 8.0.4 and criated in pg 7.4.8
-->
-->
-->On Sun, May 14, 2006 at 10:10:44AM -0300, Alejandro Michelin
-->Salomon ( Adinet ) wrote:
-->> Hi :
-->>
-->> I criated some function in my pg 8.0.4, they function as spected.
-->>
-->> But when i try tu criate this functions in my hosting that has pg
-->> 7.4.8, i can not do. I read documentation an i see some
-->disferences.
-->
--><snip>
-->
-->> How i need change, to get this function runing in pg 7.4.8 ?
-->
-->Well, since you havn't told us the error message it's hard
-->to tell the problem. At a guess though, 7.4 doesn't
-->understand dollar-quoting.
-->
-->Have a nice day,
-->--
-->Martijn van Oosterhout   <kleptog@svana.org>
-->http://svana.org/kleptog/
-->> From each according to his ability. To each according to
-->his ability
-->> to litigate.
-->
-->--
-->No virus found in this incoming message.
-->Checked by AVG Free Edition.
-->Version: 7.1.392 / Virus Database: 268.5.6/339 - Release
-->Date: 14/5/2006
-->
-->
-->

--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.392 / Virus Database: 268.5.6/339 - Release Date: 14/5/2006




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.392 / Virus Database: 268.5.6/339 - Release Date: 14/5/2006

The first thing to check is whether the hosting service has installed PL/pgsql
as a language. If you have sufficient privileges you could try 'select
lanname from pg_languages' to see if it is installed.
On Sunday 14 May 2006 07:08 am, Alejandro Michelin Salomon ( Adinet ) 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'
>
> This last syntax has error, but i can't find.
>
> Can you help me ?
>
> Alejandro MSG <alejmsg@adinet.com.uy>
> Porto Alegre
> Brasil
>
>
> -->-----Mensagem original-----
> -->De: pgsql-general-owner@postgresql.org
> -->[mailto:pgsql-general-owner@postgresql.org] Em nome de
> -->Martijn van Oosterhout
> -->Enviada em: domingo, 14 de maio de 2006 10:38
> -->Para: Alejandro Michelin Salomon ( Adinet )
> -->Cc: Pgsql-General
> -->Assunto: Re: [GENERAL] Diferences between functions criated
> -->in pg 8.0.4 and criated in pg 7.4.8
> -->
> -->
> -->On Sun, May 14, 2006 at 10:10:44AM -0300, Alejandro Michelin
> -->Salomon ( Adinet ) wrote:
> -->> Hi :
> -->>
> -->> I criated some function in my pg 8.0.4, they function as spected.
> -->>
> -->> But when i try tu criate this functions in my hosting that has pg
> -->> 7.4.8, i can not do. I read documentation an i see some
> -->disferences.
> -->
> --><snip>
> -->
> -->> How i need change, to get this function runing in pg 7.4.8 ?
> -->
> -->Well, since you havn't told us the error message it's hard
> -->to tell the problem. At a guess though, 7.4 doesn't
> -->understand dollar-quoting.
> -->
> -->Have a nice day,
> -->--
> -->Martijn van Oosterhout   <kleptog@svana.org>
> -->http://svana.org/kleptog/
> -->> From each according to his ability. To each according to
> -->his ability
> -->> to litigate.
> -->
> -->--
> -->No virus found in this incoming message.
> -->Checked by AVG Free Edition.
> -->Version: 7.1.392 / Virus Database: 268.5.6/339 - Release
> -->Date: 14/5/2006
> -->
> -->
> -->

--
Adrian Klaver
aklaver@comcast.net

Adrian :

I make some changes, based in documentation that i find in the web, and i
have this error :

ERROR:  language "plpgsql" does not exist

Seems this guys does not put pg/plsql in the instalation.

I have a question, is not pg/plsql core part in postgresql?
If not is core funcionality, is not time to be pg/plsql part off the core.
Postgresql without pg/plsql instaled, is like a Oracle without pl/sql.

Thanks for help.

Alejandro MSG <alejmsg@adinet.com.uy>
Porto Alegre
Brasil

-->-----Mensagem original-----
-->De: Adrian Klaver [mailto:aklaver@comcast.net]
-->Enviada em: domingo, 14 de maio de 2006 11:50
-->Para: pgsql-general@postgresql.org
-->Cc: Alejandro Michelin Salomon ( Adinet ); 'Martijn van Oosterhout'
-->Assunto: Re: RES: [GENERAL] Diferences between functions
-->criated in pg 8.0.4 and criated in pg 7.4.8
-->
-->
-->The first thing to check is whether the hosting service has
-->installed PL/pgsql
-->as a language. If you have sufficient privileges you could
-->try 'select
-->lanname from pg_languages' to see if it is installed.
-->On Sunday 14 May 2006 07:08 am, Alejandro Michelin Salomon (
-->Adinet ) 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'
-->>
-->> This last syntax has error, but i can't find.
-->>
-->> Can you help me ?
-->>
-->> Alejandro MSG <alejmsg@adinet.com.uy>
-->> Porto Alegre
-->> Brasil
-->>
-->>
-->> -->-----Mensagem original-----
-->> -->De: pgsql-general-owner@postgresql.org
-->> -->[mailto:pgsql-general-owner@postgresql.org] Em nome de
-->Martijn van
-->> -->Oosterhout Enviada em: domingo, 14 de maio de 2006 10:38
-->> -->Para: Alejandro Michelin Salomon ( Adinet )
-->> -->Cc: Pgsql-General
-->> -->Assunto: Re: [GENERAL] Diferences between functions criated
-->> -->in pg 8.0.4 and criated in pg 7.4.8
-->> -->
-->> -->
-->> -->On Sun, May 14, 2006 at 10:10:44AM -0300, Alejandro Michelin
-->> -->Salomon ( Adinet ) wrote:
-->> -->> Hi :
-->> -->>
-->> -->> I criated some function in my pg 8.0.4, they function
-->as spected.
-->> -->>
-->> -->> But when i try tu criate this functions in my hosting
-->that has pg
-->> -->> 7.4.8, i can not do. I read documentation an i see some
-->> -->disferences.
-->> -->
-->> --><snip>
-->> -->
-->> -->> How i need change, to get this function runing in pg 7.4.8 ?
-->> -->
-->> -->Well, since you havn't told us the error message it's
-->hard to tell
-->> -->the problem. At a guess though, 7.4 doesn't understand
-->> -->dollar-quoting.
-->> -->
-->> -->Have a nice day,
-->> -->--
-->> -->Martijn van Oosterhout   <kleptog@svana.org>
-->> -->http://svana.org/kleptog/
-->> -->> From each according to his ability. To each according to
-->> -->his ability
-->> -->> to litigate.
-->> -->
-->> -->--
-->> -->No virus found in this incoming message.
-->> -->Checked by AVG Free Edition.
-->> -->Version: 7.1.392 / Virus Database: 268.5.6/339 - Release
-->> -->Date: 14/5/2006
-->> -->
-->> -->
-->> -->
-->
-->--
-->Adrian Klaver
-->aklaver@comcast.net
-->
-->
-->--
-->No virus found in this incoming message.
-->Checked by AVG Free Edition.
-->Version: 7.1.392 / Virus Database: 268.5.6/339 - Release
-->Date: 14/5/2006
-->
-->
-->--
-->No virus found in this incoming message.
-->Checked by AVG Free Edition.
-->Version: 7.1.392 / Virus Database: 268.5.6/339 - Release
-->Date: 14/5/2006
-->
-->

--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.392 / Virus Database: 268.5.6/339 - Release Date: 14/5/2006




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.392 / Virus Database: 268.5.6/339 - Release Date: 14/5/2006

The full explanation can be found here-
http://www.postgresql.org/docs/7.4/interactive/xplang.html

The short answer is that the language is included but needs to be installed.
This is left to the discretion of the database administrator.

On Sunday 14 May 2006 08:18 am, Alejandro Michelin Salomon ( Adinet ) wrote:
> Adrian :
>
> I make some changes, based in documentation that i find in the web, and i
> have this error :
>
> ERROR:  language "plpgsql" does not exist
>
> Seems this guys does not put pg/plsql in the instalation.
>
> I have a question, is not pg/plsql core part in postgresql?
> If not is core funcionality, is not time to be pg/plsql part off the core.
> Postgresql without pg/plsql instaled, is like a Oracle without pl/sql.
>
> Thanks for help.
>
> Alejandro MSG <alejmsg@adinet.com.uy>
> Porto Alegre
> Brasil
>

--
Adrian Klaver
aklaver@comcast.net

On 5/14/06, Alejandro Michelin Salomon ( Adinet ) <alejmsg@adinet.com.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