Re: pl/perl Documentation

Hi all

I'm trying to find documentation for postgres/plperl.

the online postgres documentation has only two examples in it and i am
struggling to find any more that i can make sense of. also most of the
documents i find are about installing pl/perl. this i have done and tested
with the examples found in the online documentation.

i am a complete newbie to both perl and postgres functions, so the
documentation will almost have to have to draw pictures.

i am trying to build a function that strips whitespace.

with the following

CREATE or REPLACE FUNCTION remspace(TEXT) RETURN TEXT
AS '
@_ =~ s/\s*//;
return @_'
LANGUAGE 'plperl';

i keep getting:

parser: parse error at or near "return"

no matter what i do. i have a feeling this means that i have a syntax error
some where in the function, but can't figger out where. is there any way of
getting a more detailed error description?

duncan

"Duncan Adams (DNS)" <duncan(dot)adams(at)vcontractor(dot)co(dot)za> writes:
> CREATE or REPLACE FUNCTION remspace(TEXT) RETURN TEXT
> AS '
> @_ =~ s/\s*//;
> return @_'
> LANGUAGE 'plperl';

I'm not much of a Perl hacker, but even I can see that this is not good
Perl. You need a semicolon to finish the return statement, and I think
you want to manipulate the first element of the @_ array, not the whole
array. So something like

CREATE or REPLACE FUNCTION remspace(TEXT) RETURN TEXT AS '
$_[0] =~ s/\s*//;
return $_[0];
' LANGUAGE 'plperl';

would probably do what you want.

I'd recommend getting hold of a book about Perl.

regards, tom lane

So as a perl monger I'll pitch in. I don't use PL/Perl because I know not
to expect ISPs to support it - I haven't actually used PL/Perl. I do use
PL/SQL and Perl seperately.

Here's a cleaned up version:

CREATE FUNCTION remspace(TEXT) RETURNS TEXT AS '
return map { s/\s*// } @_;
' LANGUAGE 'plperl' WITH (isstrict);

You may have to prepend that backslash - it might be interpreted as s/s*//
which isn't right. Perform SELECT prosrc FROM pg_proc WHERE pronam =
'remspace' to see the way it came through.

Now for the perl bits:

If you want to apply the regex to an array you have to iterate over it
somehow. In this case the map operation just applies the code block
against each element in the array and returns the result.

The 'return @_' statement may or may not need a semicolon. Perl doesn't
require you use semi-colons where they aren't needed (generally). The
PL/Perl interpreter might require it anyway if it transposes your code
into other bits of code. It's just a good idea to have the semicolon
though it might not be causing you a problem.

Joshua b. Jore
http://www.greentechnologist.org

On Wed, 22 May 2002, Tom Lane wrote:

> "Duncan Adams (DNS)" <duncan(dot)adams(at)vcontractor(dot)co(dot)za> writes:
>
> I'm not much of a Perl hacker, but even I can see that this is not good
> Perl. You need a semicolon to finish the return statement, and I think
> you want to manipulate the first element of the @_ array, not the whole
> array. So something like
>
> CREATE or REPLACE FUNCTION remspace(TEXT) RETURN TEXT AS '
> $_[0] =~ s/\s*//;
> return $_[0];
> ' LANGUAGE 'plperl';
>
> would probably do what you want.
>
> I'd recommend getting hold of a book about Perl.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

"Joshua b. Jore" <josh(at)greentechnologist(dot)org> writes:
> You may have to prepend that backslash - it might be interpreted as s/s*//
> which isn't right.

Oh, of course --- the backslash has to be doubled to get through the
string-literal parser.

regards, tom lane

> CREATE FUNCTION remspace(TEXT) RETURNS TEXT AS '
> return map { s/\s*// } @_;
> ' LANGUAGE 'plperl' WITH (isstrict);

This may or may not do what you're looking for.
s/\s*// or s/\s+// removes only the spaces from the first occurrence to
the before first non-space character.

To remove all the spaces from the string then:
s/\s+//g

To strip or trim the beginning and ending spaces.
s/^\s+//
s/\s+$//

Tom

> > "Duncan Adams (DNS)" <duncan(dot)adams(at)vcontractor(dot)co(dot)za> writes:
> >
> > I'm not much of a Perl hacker, but even I can see that this is not good
> > Perl. You need a semicolon to finish the return statement, and I think
> > you want to manipulate the first element of the @_ array, not the whole
> > array. So something like
> >
> > CREATE or REPLACE FUNCTION remspace(TEXT) RETURN TEXT AS '
> > $_[0] =~ s/\s*//;
> > return $_[0];
> > ' LANGUAGE 'plperl';
> >
> > would probably do what you want.
> >
> > I'd recommend getting hold of a book about Perl.