英文:
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:
答案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$;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论