CODEWITHSUNDEEP
sqlpostgresqlfunction
nstuyvesant
nstuyvesant

Reputation: 1516

PostgreSQL Function returning JSON takes name of function

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".

enter image description here

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?

Upvotes: 0

Views: 249

Answers (1)

Johannes H.
Johannes H.

Reputation: 6167

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.

Upvotes: 1

Related Questions