0

I have a table called Listing with fields type + typeCustom where type is a strict selection of values and when type is set to value "custom" I want to be able to dynamically resolve this as whatever is in my typeCustom-field.

SELECT 
   (CASE WHEN "type" = 'custom' THEN "typeCustom" ELSE "type" END) as "typeResolved"
FROM "Listing"

My naive way and quick googling tells me I should do something like this. 

CREATE FUNCTION Listing_typeResolved(Listing_row "Listing")
RETURNS TEXT 
LANGUAGE sql
STABLE AS $$
  SELECT (CASE WHEN "type" = 'custom' THEN "typeCustom" ELSE "type" END)
$$

However, it gives me the following error:

ERROR:  missing FROM-clause entry for table "Listing"
LINE 5:   SELECT (CASE WHEN "Listing"."type" = 'custom' THEN "Listin...
                            ^

Side-note: I'm using Hasura so I want to do this in order to expose a computed field.

Alex
  • 1,689
  • 18
  • 27
  • why are you using `"` ? remove it and should work fine. – zealous Jun 08 '20 at 08:02
  • It doesn't work either - AFAIK I need to when having tables that aren't all lowercase. But with that logic my `SELECT`-example wouldn't work either. When removing quotes I get "ERROR: type listing does not exist". – Alex Jun 08 '20 at 08:23

1 Answers1

1

Try

create function "Listing_typeResolved"(arg_type text, arg_typecustom text) returns text as
$$
 select case when arg_type = 'custom' then arg_type else arg_typecustom end;
$$
language sql immutable strict;

and then you can use it in queries like this

select "Listing_typeResolved"("type", "typeCustom") as "typeResolved" from "Listing";

to encapsulate the type resolution logic (no matter how simple it is) in a function.

Please consider a completely different approach - create/use a view:

create view "ListingV" as 
  select case when "type" = 'custom' then "typeCustom" else "type" end as "typeResolved",
  <other columns and clauses>
  from "Listing";

Hope this helps.

Stefanov.sm
  • 11,215
  • 2
  • 21
  • 21
  • This is beautiful, but unfortunately, hasura only allows me to put in 1 input argument in the function. But I might do a view.. – Alex Jun 08 '20 at 12:25
  • I'm pretty sure hasura lets you add multiple arguments per function. What problem did you run into with adding more than one argument to a function? – Xetera Jun 09 '20 at 04:27