PostgreSQL Function returning JSON takes name of function

Question

This PostgreSQL function successfully returns JSON; however, the function name is associated with the result:

CREATE OR REPLACE FUNCTION public.find_classes(
    search_text character varying,
    current_page integer DEFAULT 1,
    page_size integer DEFAULT 10,
    sort_by character varying DEFAULT 'name'::character varying,
    direction character varying DEFAULT 'ASC'::character varying
)
RETURNS json AS
$$
  WITH classes_found AS (
    SELECT
      classes._id AS id, classes.name, classes.description, classes.active
    FROM classes
    WHERE search_text IS NULL OR search_text = '' OR LOWER(classes.name) LIKE LOWER(search_text) || '%'
    ORDER BY
      CASE 
        WHEN direction = 'ASC' THEN
          CASE
            WHEN sort_by = 'name' THEN classes.name
            WHEN sort_by = 'active' THEN classes.active::varchar
          END
      END ASC,
      CASE
        WHEN direction = 'DESC' THEN
          CASE
            WHEN sort_by = 'name' THEN classes.name
            WHEN sort_by = 'active' THEN classes.active::varchar
          END
      END DESC
  )
  SELECT json_build_object(
      'totalItems',(SELECT COUNT(*) FROM classes_found),
      'currentPage', current_page,
      'pageSize', page_size,
      'classes', COALESCE((SELECT json_agg(l.*) FROM (SELECT classes_found.* FROM classes_found LIMIT page_size OFFSET ((current_page - 1) * page_size)) l ), '[]')
    ) AS results;
$$ LANGUAGE SQL;

As far as both pgAdmin and node-postgres go, the data returned looks like one row with a JSON column named "find_classes".

If it has to have a name, I want it to be "results" so I can handle it on on the calling side in a more modular way.

In the SELECT at the bottom, "AS results" didn't help. I also tried writing it in plpgsql but that didn't give me any finer-grained control over this.

Here's the code that calls the PostgreSQL function...

import pg from 'pg'

const pgNativePool = new pg.native.Pool({
  max: 10, // default
  connectionString: <string> import.meta.env.VITE_DATABASE_URL,
  ssl: {
    rejectUnauthorized: false
  }
})

type QueryResponse = (sql: string, params?: Array<number|string|boolean|StudioLocation>) => Promise<pg.QueryResult<any>>
const query: QueryResponse = (sql: string, params?: Array<number|string|boolean>) => pgNativePool.query(sql, params)

type FindLocationsResponse = (searchText: string | null, currentPage: number | null, pageSize: number | null, sortBy: string | null, direction: string | null) => Promise<{ body: ClassSearchResults }>
const find: FindLocationsResponse = async (searchText, currentPage, pageSize, sortBy, direction) => {
  const sql = `SELECT * FROM find_classes(search_text:=$1, current_page:=$2, page_size:=$3, sort_by:=$4, direction:=$5);`
  const { rows } = await query(sql, [searchText, currentPage, pageSize, sortBy, direction])
  return { body: <ClassSearchResults> rows[0].find_classes }
}

Any idea how to change the column name returned?

How are you calling the function (in the calling side)?

Salman Arshad
– Salman Arshad

2021-08-12 15:06:59 +00:00
Commented Aug 12, 2021 at 15:06 — Salman Arshad
– Salman Arshad, Commented Aug 12, 2021 at 15:06
Hi @SalmanA, I've added the calling code above.

nstuyvesant
– nstuyvesant

2021-08-12 15:15:35 +00:00
Commented Aug 12, 2021 at 15:15 — nstuyvesant
– nstuyvesant, Commented Aug 12, 2021 at 15:15

Johannes H. · Accepted Answer · 2021-08-12 15:19:36Z

1

Name of output columns is generated by the clause that generates the output. So if you want to change it, you can't do that in the function but in the clause using it.

Something like SELECT find_classes(...) AS results; will do.

answered Aug 12, 2021 at 15:19

Johannes H.

6,1671 gold badge23 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

nstuyvesant Over a year ago

Thank you -I figured there'd have to be a way!

Collectives™ on Stack Overflow

PostgreSQL Function returning JSON takes name of function

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related