how to convert this sql "select id, 3 as level from t_lps_factory“ to postgrest?

[jinguo-yin]

Environment

• PostgreSQL version: 13
• PostgREST version: 11.0.1
• Operating system: macos

Description of issue

I want use postgrest to implement this sql: select id, 1 as level from t_lps_factory;
the url is: localhost:4000/t_lps_factory?select=id, 1:level
but i get error:
{
"code": "42703",
"details": null,
"hint": null,
"message": "字段 t_lps_factory.level 不存在"
}

[laurenceisla]

PostgREST won't let you select extra columns that do not exist in the table (t_lps_factory in this case). In order to add extra columns to the same table you'll need to do so by creating a computed field, otherwise, create an entire new VIEW.

[jinguo-yin]

Thank reply，but i use multiple relationship table to create tree data struct dynamic, so, the sql is create by funciton,eg:

  const tableRecurve = (level: number, tabs: string[]) => {  
    let sqlStr = '';
    let lvl = level + 1;
    if(tabs.length === 1) {
      sqlStr += `children:${tabs[0]}(*,level-${lvl}:id)`;
    }else{
      const temp = tabs.filter((it, id) => id !== 0);
      sqlStr = `children:${tabs[0]}(*,level-${lvl}:id,${tableRecurve(lvl, temp)})`;
    }
      return sqlStr;
  }
  const temp = tabs.filter((it, idx) => idx !== 0);
  const sql = tableRecurve(1, temp);   
  let sqlStr = tabs[0];
  sqlStr += `?select=*,level-${1}:id,`;
  sqlStr += sql;

result is below, i use id placehoder the 'level', parse level-2 to level: 2 in function:

t_lps_factory?select=*,level-1:id,children:t_lps_plant(*,level-2:id,children:t_lps_line(*,level-3:id)) 

[laurenceisla]

That resulting query looks OK to me, it shouldn't giver any errors. I don't quite understand this:

parse level-2 to level: 2 in function

I'm kinda lost on what you're trying to achieve here. Do you have an example of the result you want from the request you posted above?

[jinguo-yin]

level:1 is tree depth, but i can not using "1 as level" in postgrest statement.so i alias level-1/level-2..., the ID is unusable in my sql level-1:id, so i must parse level-1 to level: 1 after get sql query result.
it want a solution to get tree depth only using postgrest.

[jinguo-yin]

thanks reply
next sql can correct execute:
t_lps_factory?select=,level-1:id,children:t_lps_plant(,level-2:id,children:t_lps_line(*,level-3:id))

but level-1, level-2...is not i wanted.this field is tree's depth.so i hope to get result is: leve:1, level: 2. so, i must process the result by extra parse function to parse level-1:id to level:1.
i want to get "level: 1" only by select not using process function.

[laurenceisla]

If I understand correctly, you need something like this?

[{
  "level": 1,
  "id": 100,
  "children": {
    "level": 2,
    "id": 200,
    ...
  }
}]

If that's so, then no, that is not possible to do directly in the query string. A (very ugly) workaround that I can think of is to create many Computed Fields for each table. Something like:

CREATE FUNCTION level1(t_lps_factory)
RETURNS integer AS $$
  SELECT 1;
$$ LANGUAGE SQL;

CREATE FUNCTION level1(t_lps_plant)
RETURNS integer AS $$
  SELECT 1;
$$ LANGUAGE SQL;

CREATE FUNCTION level2(t_lps_plant)
RETURNS integer AS $$
  SELECT 2;
$$ LANGUAGE SQL;

-- And the same for the other tables

Then you can select it directly like:

t_lps_factory?select=,level:level1,children:t_lps_plant(level:level2,children:t_lps_line(*,level:level3:id))

Not sure if this is a good alternative. Maybe you can do something in your frontend to manage the original data you're getting.

[jinguo-yin]

OK, I parse level number in frontend.
thanks your reply.

[steve-chavez]

Seems like this could fit into a recursive query? If it's generic enough, we could have a special media type like:

GET /my_tree_table

Accept: application/vnd.pgrst.recursive-relationship

Which would generate the SQL:

WITH RECURSIVE ...

[jinguo-yin]

Yes，my solution is multiple level relationship using table.eg: factory table->plant table->line table.
in practice this is manufacture factory model.

at frontend, I will render tree by the query result.so i must get level(factory is first level, plant is second level ...).

of course, I can use children property to compute the level at frontend. but it is nice if i get level by query sql.

your solution that process data at frontend can solve my question.