469,658 Members | 1,750 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,658 developers. It's quick & easy.

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

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
1 105953
Slapo
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.

Similar topics

2 posts views Thread by Andrew Thompson | last post: by
4 posts views Thread by Stephen Poley | last post: by
70 posts views Thread by Ben Pfaff | last post: by
3 posts views Thread by yawnmoth | last post: by
3 posts views Thread by shakthi | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.