pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFOR-IN-EXECUTE, why fail?
3+ messages / 3 participants
[nested] [flat]
* FOR-IN-EXECUTE, why fail?
@ 2004-07-14 15:13 Marcos Medina <mmarencibia@yahoo.es>
0 siblings, 2 replies; 3+ messages in thread
From: mmarencibia@yahoo.es @ 2004-07-14 15:13 UTC (permalink / raw)
To: " <pgsql-general@postgresql.org.pgsql-novice>
I wrote the following:
CREATE OR REPLACE FUNCTION escritorio.seq_valor_actual( text)
RETURNS integer AS '
DECLARE
secuencia ALIAS FOR $1;
valor_actual integer := 0;
v_query text;
actual integer;
BEGIN
RAISE NOTICE ''el parametro tiene el siguiente valor %'' ,secuencia;
v_query := ''SELECT last_value FROM '' || quote_ident(secuencia);
RAISE NOTICE ''la sentencia a ejecutar es %'' ,v_query;
FOR actual IN EXECUTE v_query LOOP
valor_actual := actual;
END LOOP;
RETURN valor_actual;
END;
'LANGUAGE 'plpgsql';
And i call:
select seq_valor_actual('s_id_reserva');
The s_id_reserva is a sequence. The error is the following:
WARNING: plpgsql: ERROR during compile of seq_valor_actual near line 12
ERROR: missing .. at end of SQL expression
I think the problem is in the FOR but i see correct all.
Can i help me?
Any idea?
Tahnks....
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: FOR-IN-EXECUTE, why fail?
@ 2004-07-17 09:49 Gaetano Mendola <mendola@bigfoot.com>
parent: Marcos Medina <mmarencibia@yahoo.es>
1 sibling, 0 replies; 3+ messages in thread
From: Gaetano Mendola @ 2004-07-17 09:49 UTC (permalink / raw)
To: " <pgsql-general@postgresql.org.pgsql-novice>
Marcos Medina wrote:
> I wrote the following:
> CREATE OR REPLACE FUNCTION escritorio.seq_valor_actual( text)
> RETURNS integer AS '
> DECLARE
> secuencia ALIAS FOR $1;
> valor_actual integer := 0;
> v_query text;
> actual integer;
> BEGIN
> RAISE NOTICE ''el parametro tiene el siguiente valor %'' ,secuencia;
>
> v_query := ''SELECT last_value FROM '' || quote_ident(secuencia);
> RAISE NOTICE ''la sentencia a ejecutar es %'' ,v_query;
>
> FOR actual IN EXECUTE v_query LOOP
> valor_actual := actual;
> END LOOP;
>
> RETURN valor_actual;
>
> END;
> 'LANGUAGE 'plpgsql';
>
> And i call:
> select seq_valor_actual('s_id_reserva');
>
>
> The s_id_reserva is a sequence. The error is the following:
> WARNING: plpgsql: ERROR during compile of seq_valor_actual near line 12
> ERROR: missing .. at end of SQL expression
>
> I think the problem is in the FOR but i see correct all.
>
> Can i help me?
>
> Any idea?
You shall declare actual as RECORD and perform inside the loop:
valor_actual = actual.last_value;
Regards
Gaetano Mendola
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: FOR-IN-EXECUTE, why fail?
@ 2004-07-19 04:23 Tom Lane <tgl@sss.pgh.pa.us>
parent: Marcos Medina <mmarencibia@yahoo.es>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2004-07-19 04:23 UTC (permalink / raw)
To: mmarencibia@yahoo.es; +Cc: pgsql-novice@postgresql.org
mmarencibia@yahoo.es (Marcos Medina) writes:
> I wrote the following:
> CREATE OR REPLACE FUNCTION escritorio.seq_valor_actual( text)
> DECLARE
> ...
> actual integer;
> BEGIN
> ...
> FOR actual IN EXECUTE v_query LOOP
The loop variable has to be a record or row variable, not an integer.
FWIW, 7.5 will give a better error message for this common mistake.
In CVS tip I get
ERROR: loop variable of loop over rows must be a record or row variable at or near "LOOP" at character 416
LINE 14: FOR actual IN EXECUTE v_query LOOP
^
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-07-19 04:23 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-07-14 15:13 FOR-IN-EXECUTE, why fail? Marcos Medina <mmarencibia@yahoo.es>
2004-07-17 09:49 ` Gaetano Mendola <mendola@bigfoot.com>
2004-07-19 04:23 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox