如何在 PostgreSQL 中返回表时省略记录?

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

How can I omit records when returning a table in postgresql?

问题

抱歉,我无法理解代码或提供代码的翻译。

英文:

I have a problem when I try to execute a function where, depending on the result of another function, it returns a record or not. What I want to do is that when the function ("function_typecar" in the example) returns 1 as a result, it returns the record, otherwise no. The problem is when the condition is not met. It repeats the same record until there is one that meets the condition again. Is there a way to skip returning the records when the condition is not met? I leave an example of this situation

  1. CREATE OR REPLACE FUNCTION schema.function_test
  2. RETURNS TABLE(id_r INTEGER, name_r CHARACTER VARYING, year_r INTEGER) AS
  3. $BODY$
  4. DECLARE
  5. sql_record RECORD;
  6. type_car INTEGER;
  7. BEGIN
  8. SELECT id, name, year
  9. FROM car
  10. FOR sql_record IN
  11. SELECT id, name, year
  12. FROM car
  13. LOOP
  14. SELECT type INTO type_car FROM function_typecar(sql_record.id);
  15. IF type_car = 1 THEN
  16. id_r := sql_record.id;
  17. name_r := sql_record.name;
  18. year_r := sql_record.year;
  19. END IF;
  20. RETURN NEXT;
  21. END LOOP;
  22. END; $BODY$

This is the result that I want to avoid and that only brings me those that meet the IF condition:

如何在 PostgreSQL 中返回表时省略记录?

答案1

得分: 1

CREATE OR REPLACE FUNCTION function_test()
RETURNS TABLE(id_r INTEGER, name_r CHARACTER VARYING, year_r INTEGER)
LANGUAGE SQL
AS
$BODY$
SELECT id, name, year
FROM car
JOIN function_typecar(id) ON type = 1;
$BODY$;

英文:

Something like this should work, without a LOOP and in plain SQL:

  1. CREATE OR REPLACE FUNCTION function_test()
  2. RETURNS TABLE(id_r INTEGER, name_r CHARACTER VARYING, year_r INTEGER)
  3. LANGUAGE SQL
  4. AS
  5. $BODY$
  6. SELECT id, name, year
  7. FROM car
  8. JOIN function_typecar(id) ON type = 1;
  9. $BODY$;

huangapple
  • 本文由 发表于 2023年2月10日 03:28:02
  • 转载请务必保留本文链接:https://go.coder-hub.com/75403516.html
匿名

发表评论

匿名网友

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

确定