Thread: PL/pgSQL in 8.0
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? Particularly, the use of RECORD? It looks like
RECORD doesn't like to get assigned a single column:
CONTEXTO: 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
ERROR: el operador no existe: record = integer
I did a dumapall and then recovered, as I always do, with psql, before the
upgrade.
--
11:55:33 up 36 days, 20:41, 2 users, load average: 1.73, 1.35, 1.25
---------------------------------------------------------
Lic. Martín Marqués | SELECT 'mmarques' ||
Centro de Telemática | '@' || 'unl.edu.ar';
Universidad Nacional | DBA, Programador,
del Litoral | Administrador
---------------------------------------------------------
=?iso-8859-1?q?Mart=EDn_Marqu=E9s?= <martin@bugs.unl.edu.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.
regards, tom lane
El Lun 08 Ago 2005 12:49, Tom Lane escribió:
> =?iso-8859-1?q?Mart=EDn_Marqu=E9s?= <martin@bugs.unl.edu.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
---------------------------------------------------------
=?iso-8859-1?q?Mart=EDn_Marqu=E9s?= <martin@bugs.unl.edu.ar> writes:
> 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:
8.0 is able to pass RECORD variables as-a-whole down into executed SQL
commands, which earlier versions didn't do, thus masking the problem.
regards, tom lane