By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,558 Members | 1,399 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,558 IT Pros & Developers. It's quick & easy.

Pseudo-type record seen as column by an older PostgreSQL version

P: 8
Hello everyone,

I could use some help with this function I've created (see below) that runs flawlessly on PostgreSQL 8.3.6 on my development machine but ends with an error on the production server that has PostgreSQL 7.4.8 installed (see below).

My function:
Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE FUNCTION generate_menu(varchar(255), char(2), varchar(255), int, varchar(255), int, int, varchar(255), varchar(255)) RETURNS text AS '
  2. DECLARE
  3.     -- declarations
  4.     link_base        ALIAS FOR $1;
  5.     glue            ALIAS FOR $2;
  6.     extra_attributes    ALIAS FOR $3;
  7.     id_parent_target    ALIAS FOR $4;
  8.     link_categories        ALIAS FOR $5;
  9.     last_id            ALIAS FOR $6;
  10.     level            ALIAS FOR $7;
  11.     translate_from        ALIAS FOR $8;
  12.     translate_to        ALIAS FOR $9;
  13.     link_categories_new    varchar(50) := '''';
  14.     category        RECORD;
  15.     subcategories        text := '''';
  16.     output_string        text := '''';
  17. BEGIN
  18.     -- function body
  19.     SELECT id, id_parent, name INTO category FROM is_categories WHERE id>last_id AND status=''1'' AND id_parent=id_parent_target LIMIT 1;
  20.     WHILE category IS NOT NULL LOOP
  21.         link_categories_new := link_categories || glue || category.id;
  22.         IF substring(link_categories_new from 1 for 2) = glue THEN
  23.             link_categories_new := overlay((link_categories || glue || category.id) placing '''' FROM 1 FOR 2);
  24.         END IF;
  25.  
  26.         output_string := output_string || ''<div><span class="level'' || level || ''"><a href="'' || link_base || link_categories_new || glue || translate(category.name, translate_from, translate_to) || ''" title="'' || category.name || ''" '' || extra_attributes || ''>'' || category.name || ''</a></span></div>'';
  27.         SELECT * INTO subcategories FROM generate_menu(link_base, glue, extra_attributes, category.id, link_categories_new, category.id, (level + 1), translate_from, translate_to);
  28.         output_string := output_string || subcategories;
  29.         SELECT id, id_parent, name INTO category FROM is_categories WHERE id>category.id AND status=''1'' AND id_parent=id_parent_target LIMIT 1;
  30.     END LOOP;
  31.  
  32.     RETURN output_string;
  33. END;
  34. ' LANGUAGE plpgsql;
PostgreSQL 7.4.8 error:
Expand|Select|Wrap|Line Numbers
  1. ERROR:  column "category" does not exist
  2. CONTEXT:  PL/pgSQL function "generate_menu" line 19 at while
  3.  
  4. In statement:
  5. select * from generate_menu('index.php?/eshop/category/', '__', '', 0, '', 0, 1, 'čďěľĺňŕřťůČĎĚĽĹŇŔŘŤŮݎ /', 'aacdeeillnoorrstuuyzAACDEEILLNOORRSTUUYZ_-');
Category mentioned in the error is of the pseudo-type record and I can't see why PostgreSQL would think it's a column.

Any help would be appreciated as I can't find anything on this.

Thanks in advance :)
Apr 11 '09 #1
Share this Question
Share on Google+
1 Reply


P: 8
I'll reply to myself here:
changing:
Expand|Select|Wrap|Line Numbers
  1. WHILE category IS NOT NULL LOOP
to:
Expand|Select|Wrap|Line Numbers
  1. WHILE category.id IS NOT NULL LOOP
solved the problem.
Apr 11 '09 #2

Post your reply

Sign in to post your reply or Sign up for a free account.