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

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

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

CREATE OR REPLACE FUNCTION schema.function_test
  RETURNS TABLE(id_r INTEGER, name_r CHARACTER VARYING, year_r INTEGER) AS
$BODY$
  
DECLARE 
	sql_record RECORD;
	type_car INTEGER;
BEGIN  
	SELECT id, name, year
	FROM car


	FOR sql_record IN
		SELECT id, name, year
		FROM car
	LOOP
		SELECT type INTO type_car FROM function_typecar(sql_record.id);

		IF type_car = 1 THEN
			id_r := sql_record.id;
			name_r := sql_record.name;
			year_r := sql_record.year;			

		END IF;

		RETURN NEXT;

	END LOOP;
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:

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$;

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:

确定