Re: PL/pgSQL in 8.0

El Lun 08 Ago 2005 12:49, Tom Lane escribió:
> =?iso-8859-1?q?Mart=EDn_Marqu=E9s?= <martin(at)bugs(dot)unl(dot)edu(dot)ar> writes:
> > I upgraded a couple of servers to 8.0.3 a few weeks ago, and just found
> > out that some of the PL/pgSQL functions ar not working as they did, even
> > more, they are giving errors.
> >
> > Did something change from 7.4?
>
> You really have to give some details ... like the text of the problem
> function ... if you want help.

Don't worry, found it.

I had a DECLARED RECORD type which had the same name as the column of a table
I later used.

What really intriges me is that before the upgrade (I was using 7.4.7 and
upgraded to 8.0.3, all Debian packages) this function worked OK, even though
I see my mistake when programming it, but after switching to 8.0.3 I stated
getting this error:

prueba=> select getvencimientosancion(1);
ERROR: el operador no existe: record = integer
HINT: Ningún operador coincide con el nombre y el tipo de los argumentos.
Puede desear agregar conversiones explícitas de tipos.
CONTEXT: sentencia SQL: «SELECT nuevo_vto FROM modif_sancion WHERE $1 =
$2 ORDER BY femodif DESC LIMIT 1»
PL/pgSQL function "getvencimientosancion" line 13 at select into variables

The problem was on line 13, where I had something like this:

SELECT INTO modif_san nuevo_vto FROM modif_sancion
WHERE sancion = sancion.codigo;

Where sancion is a RECORD, which gets filled in an earlier query, but it's
also the name of a column of modif_sancion table. There "my" mistake.

--
12:45:47 up 36 days, 21:31, 2 users, load average: 1.55, 0.71, 0.88
---------------------------------------------------------
Lic. Martín Marqués | SELECT 'mmarques' ||
Centro de Telemática | '@' || 'unl.edu.ar';
Universidad Nacional | DBA, Programador,
del Litoral | Administrador
---------------------------------------------------------