Postgresql中的IF语句(IF statement in Postgresql)

Postgresql中的IF语句(IF statement in Postgresql)

我为我们的系统提供了一个饲料表

它看起来像这样：

id | owner_id | user_id | feed_type_id | object_id | created_at | updated_at

feed_type_id - 表示存储在不同表中的对象类型（文章或job_post）

object_id是对象ID本身，但如果此对象仍处于活动状态，我不知道。 所以我已经获得了不同类型对象的所有活动ID，但是当我试图用IF语句构建查询时，就像这样

SELECT * FROM feeds WHERE "owner_id" in (2, 4, 1356) AND object_id IN ( IF `feed_type_id` = 1 THEN (2,3,23,33) ELSE (4,5,77,33) end if)

它不工作。

有人能给我一个想法如何做到这一点？

I have a table with feed for our system

it's look like this:

id | owner_id | user_id | feed_type_id | object_id | created_at | updated_at

feed_type_id - represent type of object that is stored in different table (article or job_post)

object_id is the object ID itself, but I don't have a clue if this object is still active. So I have get all active id for different type of object, but when I am trying to build query with IF statement something like this

SELECT * FROM feeds WHERE "owner_id" in (2, 4, 1356) AND object_id IN ( IF `feed_type_id` = 1 THEN (2,3,23,33) ELSE (4,5,77,33) end if)

it's not working.

Can someone give me an idea how to do this?

最满意答案

IF语句不是SQL语言的一部分，而是过程语言的一部分。 因此，您不能直接在查询中使用它们。

您正在查找的查询是这样的：

SELECT * FROM feeds WHERE "owner_id" in (2, 4, 1356) AND ( ("feed_type_id" = 1 AND "object_id" IN (2, 3, 23, 33)) OR ("feed_type_id" != 1 AND "object_id" IN (4, 5, 77, 33)) )

IF statements are not part of the SQL language, but part of the procedural language. As such, you can't use them directly in queries.

The query you're looking for is something like this:

SELECT * FROM feeds WHERE "owner_id" in (2, 4, 1356) AND ( ("feed_type_id" = 1 AND "object_id" IN (2, 3, 23, 33)) OR ("feed_type_id" != 1 AND "object_id" IN (4, 5, 77, 33)) )