Hi all,
Solved my own problem. I was just misreading the error message. It was
actually TELLING me the problem (I was referencing a table with no field
called "description" in the select, but trying to use it in the loop.
Sorry for disturbing people.
As an aside, this has motivated me to add a section on error messages
into techdocs.postgresql.org (very messy at the moment, I'll fix it
tonight).
Regards and best wishes,
+ Justin Clift
Database Administrator
Justin Clift wrote:
>
> Hi all,
>
> I'm getting this error, which to me makes no sense. Running PostgreSQL
> 7.0.3 on Mandrake 7.2 (compiled from source, not the rpms).
>
> The code is in a pl/pgsql function I am writing and I can't see why it's
> complaining.
>
> This is the appropriate part of the code :
>
> <general CREATE type header>
>
> arow record;
>
> <other declarations>
>
> BEGIN
>
> <other code here, which works>
>
> FOR arow IN select transaction_payments.cashback from
> transaction_payments, payment_types where
> transaction_payments.payment_type = payment_types.idnum LOOP
> textbuf := text(arow.cashback);
> textbuf := textcat(textbuf, '' '');
> insert into receipts (receipt_num, data) values (trans_num,
> textbuf);
> END LOOP;
>
> <general END type syntax>
>
> >From the PostgreSQL log file (debug is set to 2), I am getting :
>
> query: SELECT transaction_payments.cashback from transaction_payments,
> payment_types where transaction_payments.payment_type =
> payment_types.idnum
> ERROR: record arow has no field description
> DEBUG: Last error occured while executing PL/pgSQL function
> create_receiptp3
> DEBUG: line 105 at assignment
> AbortCurrentTransaction
>
> The "arow" record type variable is used quite a lot in previous FOR
> loops in this function. This is the first FOR loop in the function that
> uses more than one table though. I suspect this may have something to
> do with it.
>
> As far as I can tell, this SQL statement is valid. Does anyone have any
> ideas how to get this to work. I have tried several variations, and the
> mailing lists don't even have a reference for this error message.
>
> Regards and best wishes,
>
> Justin Clift
> Database Administrator
