在PostgreSQL中将元素分配给具有属性的数组索引。

huangapple
go评论94阅读模式
英文:

Assign Element To Index of Array with Attribute in PostgreSSQL

问题

I have an oracle code like this:

  1. FOR loop1 IN cursor1 LOOP
  2. array1.EXTEND;
  3. array1(loop1).cur_name := cursor1.name;
  4. array1(loop1).cur_value := cursor1.value;
  5. END LOOP;

我有一个类似这样的Oracle代码：

  1. FOR loop1 IN cursor1 LOOP
  2. array1.EXTEND;
  3. array1(loop1).cur_name := cursor1.name;
  4. array1(loop1).cur_value := cursor1.value;
  5. END LOOP;

I tried to convert to PostgreSQL like this, but it's getting an error:

  1. DECLARE 
  2.   cursor1 cursor for select name, value from table;
  3.   array1   text[];
  4. BEGIN
  5.   -- Do something
  6.   ...
  8.   FOR loop1 IN cursor1 LOOP
  9.     array1[loop].cur_name := cursor1.name; --error here
  10.     array1[loop1].cur_value := cursor1.value; -- error here
  11.   END LOOP;
  13.   -- Do something
  14.   ...
  15.   RETURN;
  16. END;

我尝试将它转换成 PostgreSQL，但出现了错误：

  1. DECLARE 
  2.   cursor1 cursor for select name, value from table;
  3.   array1   text[];
  4. BEGIN
  5.   -- Do something
  6.   ...
  8.   FOR loop1 IN cursor1 LOOP
  9.     array1[loop].cur_name := cursor1.name; -- 在这里出错
  10.     array1[loop1].cur_value := cursor1.value; -- 在这里出错
  11.   END LOOP;
  13.   -- Do something
  14.   ...
  15.   RETURN;
  16. END;

Is there any method to create an array with an attribute name?
有没有办法创建具有属性名的数组？

英文:

Can I assign a value from cursor to index of array with attribute?

I have an oracle code like this :

  1. cursor cursor1 is select name, value from table;
  3. FOR loop1 IN cursor1 LOOP
  4. array1.EXTEND;
  5. array1(loop1).cur_name := cursor1.name;
  6. array1(loop1).cur_value := cursor1.value;
  7. END LOOP;

i tried to convert to postgresql like this, but it's getting error

  1. CREATE FUNCTION function_name () RETURNS something AS $$
  2. DECLARE 
  3.   cursor1 cursor for select name, value from table;
  4.   array1   text[];
  5. BEGIN
  6.   -- Do something
  7.   ...
  9.   FOR loop1 IN cursor1 LOOP
  10.     array1[loop].cur_name := cursor1.name; --error here
  11.     array1[loop1].cur_value := cursor1.value; -- error here
  12.   END LOOP;
  14.   -- Do something
  15.   ...
  16.   RETURN;
  17. END;

is there any method to create an array with attibute name?

答案1

得分: 2

Oracle函数返回一个集合（如果我没记错的话，是一个“关联数组”），但已经有一段时间了。Postgres没有集合，最接近的数据类型是数组。然而，由于您的集合包含多列，您需要创建一个用户定义类型（UDT），然后您的函数返回该类型的数组。（注意：我假设了表中的数据类型。请根据需要更正。）

  1. create type name_val as (name text, value integer);
  3. create or replace function function_name () 
  4.     returns name_val[]
  5.     language plpgsql
  6. as $$
  7. declare 
  8.   cursor1 cursor for
  9.           select name, value
  10.             from test
  11.            limit 10;  
  13.   rec     record;
  14.   array1  name_val[];  
  15.   l_name_val name_val; 
  16. begin
  17.   -- 做一些操作
  19.   for rec in cursor1
  20.   loop
  21.     l_name_val.name = rec.name;
  22.     l_name_val.value = rec.value; 
  23.     array1 = array1 || l_name_val;
  24.   end loop
  26.   -- 做一些操作
  28.   return array1;
  29. end;
  30. $$;

还有其他几种选项可以完全避免使用游标和循环。假设您确实需要返回一个数组，您可以将上述函数简化为一个单独的SQL语句：

  1. create or replace function function_name3()
  2.    returns name_val[]
  3.   language sql
  4. as $$
  5.     select array_agg((name, value)::name_val) 
  6.       from test 
  7.      limit 10;
  8. $$;

演示在这里

更新：<br/>
我注意到，在我回答后，您将问题从 for loop1 in 1 .. 10 ... 更改为 for rec in cursor1 ...，从而删除了结果行的限制。您只需删除Limit 10子句即可达到相同的效果。

英文:

The Oracle function is returning a collection (An Associative Array if I remember correctly, but its been awhile). Postgres does NOT have collections, the closest data type is an array. However since your collection contains multiple columns, you need to create a UDT (user defined type}, then your function returns an array of that type. (Note I assumed the data types in the table. Correct as deeded.)

  1. create type name_val as (name text, value integer); 
  3. create or replace function function_name () 
  4.     returns name_val[]
  5.    language plpgsql
  6. as $$
  7. declare 
  8.   cursor1 cursor for
  9.           select name, value
  10.             from test
  11.            limit 10;  
  12.         
  13.   rec     record;
  14.   array1  name_val[];  
  15.   l_name_val name_val; 
  16. begin
  17.   -- do something
  18.  
  20.   for rec in cursor1
  21.   loop
  22.     l_name_val.name = rec.name;
  23.     l_name_val.value = rec.value; 
  24.     array1 = array1 || l_name_val;
  25.   end loop;
  27.   -- do something
  28.   
  29.   return  array1;
  30. end;
  31. $$;

There are a couple other option which avoid the cursor and looping altogether. Assuming you actually need any Array returned you can reduce the above function to a single sql statement:

  1. create or replace function function_name3()
  2.    returns name_val[]
  3.   language sql
  4. as $$
  5.     select array_agg((name, value)::name_val) 
  6.       from test 
  7.      limit 10;
  8. $$;

Demo Here

UPDATE: <br/>
I noticed that subsequent to my answer you update the question from for loop1 in 1 .. 10 ... to for rec in cursor1 ... thus removing the resulting row limitation. You accomplish the same by just removing the Limit 10 clause.

huangapple
  • 本文由 发表于 2023年2月6日 07:10:06
  • 转载请务必保留本文链接：https://go.coder-hub.com/75356135.html
  • arrays
  • postgresql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定