Hi,

i've just migrated a stored procedure from MySQl to PostgreSQL and i have the following error :
ERROR: syntax error at or near "SELECT" at character 371

here is my function in PostgreSQL :
CREATE OR REPLACE FUNCTION immense_sp001(VARCHAR,VARCHAR, service_nom VARCHAR, OUT result BOOLEAN, OUT error_message VARCHAR)
RETURNS record LANGUAGE plpgsql
AS '
BEGIN
DECLARE
username varchar :=$1;
strhash varchar :=$2;
Profile_Detected INTEGER;
Service_Already_Exist INTEGER;

/* detect if the user logged in exists in database */
SELECT count(*) INTO Profile_Detected FROM profiles WHERE login=username AND pwd=strhash;
if (Profile_Detected = 1) then
/* detect if service already exists in database */
SELECT count(*)
INTO Service_Already_Exist
FROM immense.services
WHERE service_nom = service_name;
if (Service_Already_Exist = 0) then
/* to reset AUTO_INCREMENT field : service_id */
alter table services auto_increment=1;
/* service does not exist, so we can add it */
insert into immense.services
set service_name = service_nom;
set result = false;
set error_message="new service created";
else
/* service already exists in database and can not be created */
set result = true;
set error_message = "service already exists";
end if;
else
set result=true;
set error_message = "user does not exist";
end if;
END;
'

-----
What could it be ? I'm lost :-(
thanks for help.

Maileen




---------------------------------
New Yahoo! Messenger with Voice. Call regular phones from your PC and save big.

"P.M" <pmdanger@yahoo.com> writes:
> here is my function in PostgreSQL :
> CREATE OR REPLACE FUNCTION immense_sp001(VARCHAR,VARCHAR, service_nom VARCHAR, OUT result BOOLEAN, OUT error_message VARCHAR)
> RETURNS record LANGUAGE plpgsql
> AS '
> BEGIN
> DECLARE
> username varchar :=$1;
> strhash varchar :=$2;
> Profile_Detected INTEGER;
> Service_Already_Exist INTEGER;

> /* detect if the user logged in exists in database */
> SELECT count(*) INTO Profile_Detected FROM profiles WHERE login=username AND pwd=strhash;

The BEGIN goes after the variable declarations, not before them.

You're going to have some problems with those double-quoted string
literals too; that's not the correct syntax for string literals.
And you don't use "set" when assigning to a plpgsql variable.

Might be a good idea to practice on some toy functions until you've got
some familiarity with plpgsql syntax, rather than diving in with porting
many-line functions. A big function is too hard when you're trying to
fix many misconceptions at once.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

On Sat, May 27, 2006 at 08:20:47AM -0700, P.M wrote:
> i've just migrated a stored procedure from MySQl to PostgreSQL and i have the following error :
> ERROR: syntax error at or near "SELECT" at character 371
>
> here is my function in PostgreSQL :
> CREATE OR REPLACE FUNCTION immense_sp001(VARCHAR,VARCHAR, service_nom VARCHAR, OUT result BOOLEAN, OUT error_message VARCHAR)
> RETURNS record LANGUAGE plpgsql
> AS '
> BEGIN
> DECLARE

BEGIN should follow the variable declarations. See "Structure of
PL/pgSQL" in the documentation:

http://www.postgresql.org/docs/8.1/i...structure.html

The function also has other MySQLisms such as auto_increment and the
use of "set". See the PL/pgSQL and other PostgreSQL documentation
for the appropriate syntax.

--
Michael Fuhr

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Hi Tom,

in fact,i needed to move the DECLARE before begin and to adjust some autoincrement...and the problem is solved ;-)

thx,

Maileen

Tom Lane <tgl@sss.pgh.pa.us> wrote: "P.M"
writes:
> here is my function in PostgreSQL :
> CREATE OR REPLACE FUNCTION immense_sp001(VARCHAR,VARCHAR, service_nom VARCHAR, OUT result BOOLEAN, OUT error_message VARCHAR)
> RETURNS record LANGUAGE plpgsql
> AS '
> BEGIN
> DECLARE
> username varchar :=$1;
> strhash varchar :=$2;
> Profile_Detected INTEGER;
> Service_Already_Exist INTEGER;

> /* detect if the user logged in exists in database */
> SELECT count(*) INTO Profile_Detected FROM profiles WHERE login=username AND pwd=strhash;

The BEGIN goes after the variable declarations, not before them.

You're going to have some problems with those double-quoted string
literals too; that's not the correct syntax for string literals.
And you don't use "set" when assigning to a plpgsql variable.

Might be a good idea to practice on some toy functions until you've got
some familiarity with plpgsql syntax, rather than diving in with porting
many-line functions. A big function is too hard when you're trying to
fix many misconceptions at once.

regards, tom lane




---------------------------------
Yahoo! Messenger with Voice. Make PC-to-Phone Calls to the US (and 30+ countries) for 2¢/min or less.

Hi All,

This is ambarasing, but I've just noticed the following (which looks
inconsistant to inexperienced eye). Having a table:

test=> CREATE TABLE xxx (id int, info text);

With some rows in it, I try:

test=> SELECT count(1) from xxx where id=1;
count
-------
0
(1 row)

This is correct (meaning, I expected that). But when I try to fetch the
actual selector used in the query as well, I get no rows instead.

test=> SELECT count(1),id from xxx where id=1 group by id;
count | id
-------+----
(0 rows)

Is this a feature, or a bug? And in fact, is there a construct to get
both the count() and its selectors *in*case*, when the count is ZERO?

All the above in postgres 8.1.

Thenx.

--
-R

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Rafal Pietrak wrote:
> Hi All,
>
> This is ambarasing, but I've just noticed the following (which looks
> inconsistant to inexperienced eye). Having a table:
>
> test=> CREATE TABLE xxx (id int, info text);
>
> With some rows in it, I try:
>
> test=> SELECT count(1) from xxx where id=1;
> count
> -------
> 0
> (1 row)
>
> This is correct (meaning, I expected that). But when I try to fetch the
> actual selector used in the query as well, I get no rows instead.
>
> test=> SELECT count(1),id from xxx where id=1 group by id;
> count | id
> -------+----
> (0 rows)
>
> Is this a feature, or a bug? And in fact, is there a construct to get
> both the count() and its selectors *in*case*, when the count is ZERO?
>
> All the above in postgres 8.1.

It is supposed to work that way. In the first query, we have to return
a row to show you the count, while in the second query, there is no 'id'
value to show you, so we return nothing (nothing to GROUP BY).

--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

On Sat, 2006-05-27 at 14:06 -0400, Bruce Momjian wrote:
> > Is this a feature, or a bug? And in fact, is there a construct to get
> > both the count() and its selectors *in*case*, when the count is ZERO?
> >
> > All the above in postgres 8.1.
>
> It is supposed to work that way. In the first query, we have to return
> a row to show you the count, while in the second query, there is no 'id'
> value to show you, so we return nothing (nothing to GROUP BY).

But is there a way to achieve one row output with both the count() and
its selector, when the ocunt is ZERO?

I'm digging this, because it looke like I need a VIEW, that returns such
count() no matter what. And in fact the selector (which is coming from
subquery) is more important for me in that case, than the count() itself
(well, I need to distinquish zero from something, but nothing more).

Is there a way to see it?

--
-R

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Maybe this my work for you?

CREATE TABLE xxx (id int, info text);
create table xxx_grp ( id int, grp text);

insert INTO xxx_grp values ( 0, 'group0');
insert INTO xxx_grp values ( 1, 'group1');
insert INTO xxx_grp values ( 2, 'group2');

insert into xxx values ( 1, 'test1');
insert into xxx valves ( 2, 'test2a');
insert into xxx values ( 2, 'test2b');

select count(xxx.id) as cnt,
xxx.id,
xxx_grp.id as grpid,
xxx_grp.grp
from xxx
right join xxx_grp
on (xxx.id = xxx_grp.id)
group by xxx.id, grpid, xxx_grp.grp
order by xxx_grp.id;

cnt | id | grpid | grp
-----+----+-------+--------
0 | | 0 | group0
1 | 1 | 1 | group1
2 | 2 | 2 | group2


regards,

Richard Broersma Jr.



--- Rafal Pietrak <rafal@zorro.isa-geek.com> wrote:

> On Sat, 2006-05-27 at 14:06 -0400, Bruce Momjian wrote:
> > > Is this a feature, or a bug? And in fact, is there a construct to get
> > > both the count() and its selectors *in*case*, when the count is ZERO?
> > >
> > > All the above in postgres 8.1.
> >
> > It is supposed to work that way. In the first query, we have to return
> > a row to show you the count, while in the second query, there is no 'id'
> > value to show you, so we return nothing (nothing to GROUP BY).
>
> But is there a way to achieve one row output with both the count() and
> its selector, when the ocunt is ZERO?
>
> I'm digging this, because it looke like I need a VIEW, that returns such
> count() no matter what. And in fact the selector (which is coming from
> subquery) is more important for me in that case, than the count() itself
> (well, I need to distinquish zero from something, but nothing more).
>
> Is there a way to see it?
>
> --
> -R
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

On Sat, 2006-05-27 at 11:51 -0700, Richard Broersma Jr wrote:
> select count(xxx.id) as cnt,
> xxx.id,
> xxx_grp.id as grpid,
> xxx_grp.grp
> from xxx
> right join xxx_grp
> on (xxx.id = xxx_grp.id)
> group by xxx.id, grpid, xxx_grp.grp
> order by xxx_grp.id;
>
> cnt | id | grpid | grp
> -----+----+-------+--------
> 0 | | 0 | group0
> 1 | 1 | 1 | group1
> 2 | 2 | 2 | group2

The count() in my case may easyly reach 10k (so the group table wont end
up too light, and it have to have a margin), but that's something
concrete to start with.

Thenx.

--
-R

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Rafal Pietrak wrote:
> On Sat, 2006-05-27 at 14:06 -0400, Bruce Momjian wrote:
>>> Is this a feature, or a bug? And in fact, is there a construct to get
>>> both the count() and its selectors *in*case*, when the count is ZERO?
>>>
>>> All the above in postgres 8.1.
>> It is supposed to work that way. In the first query, we have to return
>> a row to show you the count, while in the second query, there is no 'id'
>> value to show you, so we return nothing (nothing to GROUP BY).
>
> But is there a way to achieve one row output with both the count() and
> its selector, when the ocunt is ZERO?
>
> I'm digging this, because it looke like I need a VIEW, that returns such
> count() no matter what. And in fact the selector (which is coming from
> subquery) is more important for me in that case, than the count() itself
> (well, I need to distinquish zero from something, but nothing more).
>
> Is there a way to see it?

SELECT dummy.id, count(xxx.id) FROM (SELECT :id as id FROM dual) as
dummy LEFT JOIN xxx using (id) GROUP BY id;

You owe the Oracle a natural left join replacement.

/Nis


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster