RE: MSSQL to PostgreSQL - Problema intentando generar ID de modo YYYYMMDD0000000X

No me esta dejando compilar esta función

CREATE OR REPLACE FUNCTION THUBAN_SP_GENERATEID() RETURNS VARCHAR
AS $$
DECLARE
item_id VARCHAR;
id INT8;
BEGIN
IF (SELECT max(seq_id) FROM thuban_seq
WHERE seq_id LIKE (to_char(now() + '1 day'::interval,
'YYYYMMDD') || '%')) IS NOT NULL

THEN
SELECT max(seq_id) INTO item_id
FROM thuban_seq
WHERE seq_id LIKE (to_char(now() + '1 day'::interval,
'YYYYMMDD') || '%');

id := substr(item_id, 9, length(item_id)-9)::INT8;
to_char(id, '8');
RAISE NOTICE 'ID SOLAMENTE CREADO DENTRO DEL IF ES
%', id;

ELSE
id:= 00000000;
to_char(id, '8');
RAISE NOTICE 'ID SOLAMENTE CREADO DENTRO DEL ELSE ES
%', id;
END IF;

-- INSERT INTO thuban_seq(seq_id) VALUES (to_char(now(),'YYYYMMDD') ||
(id+1)) RETURNING seq_id INTO item_id;
INSERT INTO thuban_seq(seq_id) VALUES (to_char(now(),'YYYYMMDD') ||
to_char(id+1,'00000000'))) RETURNING seq_id INTO item_id;

RAISE NOTICE 'EL ID CALCULADO ES %', id;

RETURN item_id;

END;
$$ LANGUAGE plpgsql;

Por esta linea

to_char(id, '8');

Que es lo malo de esa linea???

Tambien probe con to_char(id+1,'00000000') pero tampoco le gusta.

Este es el error que obtengo:

ERROR: error de sintaxis en o cerca de «to_char»
LINE 1: to_char( $1 , '00000000')
^
QUERY: to_char( $1 , '00000000')
CONTEXT: SQL statement in PL/PgSQL function "thuban_sp_generateid" near
line 14

********** Error **********

ERROR: error de sintaxis en o cerca de «to_char»
SQL state: 42601
Context: SQL statement in PL/PgSQL function "thuban_sp_generateid" near line
14

-----Mensaje original-----
De: Mariano Reingart [mailto:reingart(at)gmail(dot)com]
Enviado el: Martes, 06 de Abril de 2010 08:17 p.m.
Para: Ignacio Balcarce
CC: Alvaro Herrera; pgsql-es-ayuda(at)postgresql(dot)org
Asunto: Re: [pgsql-es-ayuda] MSSQL to PostgreSQL - Problema intentando
generar ID de modo YYYYMMDD0000000X

Para el to_char:

INSERT INTO thuban_seq(seq_id) VALUES (to_char(now(),'YYYYMMDD') ||
to_char(id+1,'00000000'))) RETURNING seq_id INTO item_id;

Y para evitar los posibles problemas como recomienda Alvaro (si
entendí bien, que varios usuarios compitan por el mismo ID, que
igualmente te daría error al insertar por ser duplicado), me parece
que tendrías que poner al principio un:

LOCK TABLE thuban_seq;

lo que bloquearía esta función si hay otro usuario concurrente (y
tendrías que hacerlo al principio de la transacción, posiblemente en
el nivel de aislamiento SERIALIZABLE).

El problema es que bloquearías también cualquier otra operación sobre
la tabla (hasta que termine la transacción), con la probable pérdida
de rendimiento.

¿No podés usar secuencias automáticas de PostgreSQL (tipo de datos
serial)?, sería más fácil y no tendrías estos problemas.

Sds

Mariano Reingart
http://www.sistemasagiles.com.ar
http://reingart.blogspot.com

2010/4/6 Ignacio Balcarce <ignacio(dot)balcarce(at)vivatia(dot)com>:
> Como podría optimizar y mejorar esta función?
>
> Soy nuevo en postgre y realmente me esta costando esto, por donde puedo
ver?
>
> Que cambios les harías y porque crees q tengo el error y como lo
> corregirías?
>
> Ignacio
>
> El Apr 6, 2010, a las 18:27, Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>
> escribió:
>
>> Ignacio Balcarce escribió:
>>
>>> BEGIN
>>>               IF (SELECT max(seq_id) FROM thuban_seq
>>>                   WHERE seq_id LIKE (to_char(now() + '1 day'::interval,
>>> 'YYYYMMDD') || '%')) IS NOT NULL
>>>
>>>                              THEN
>>>                                              SELECT max(seq_id) INTO
>>> item_id
>>>                                              FROM thuban_seq
>>>                                              WHERE seq_id LIKE
>>> (to_char(now() + '1 day'::interval, 'YYYYMMDD') || '%');
>>>
>>>                                              id := substr(item_id, 9,
>>> length(item_id)-9)::INT8;
>>>
>>>               ELSE
>>>                                              id:= 00000000;
>>>               END IF;
>>>
>>>  INSERT INTO thuban_seq(seq_id) VALUES (to_char(now(),'YYYYMMDD') ||
>>> (id+1)) RETURNING seq_id INTO item_id;
>>
>>
>> Espero que tengas algún lock externamente a la ejecución de esta
>> función, porque así como está es sujeto de condiciones de carrera.
>>
>> --
>> Alvaro Herrera
>> http://www.amazon.com/gp/registry/DXLWNGRJD34J
>> "I'm always right, but sometimes I'm more right than other times."
>>                                                 (Linus Torvalds)
>