Question 178523 in PostgreSQL Question posted in
The official documentation can be found here.

How do I return my custom composite type in PostgreSQL?

It’s my custom type.

CREATE TYPE salary_info AS (
    max_salary numeric,
    min_salary numeric
);

CREATE FUNCTION test_1(city varchar(15)) RETURNS salary_info AS $$
    SELECT 
        MAX(salary) AS min_salary,
        MIN(salary) AS max_salary
    FROM employees
        WHERE city = test_1.city;  
$$ LANGUAGE SQL;

SELECT test_1('New York');

And I get this result:
enter image description here

But this sample works:

CREATE FUNCTION test_4(city varchar(15)) RETURNS salary_info AS $$
    SELECT 1.0, 2.0
$$ LANGUAGE SQL;

SELECT test_4('New York');

Output (1.0, 2.0)

What could be wrong?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I have a problem with where clause. Not valid city.


  2. 0

    Always fully qualify identifiers in queries used in functions to protect against potential name collisions; for example:

    CREATE FUNCTION test_1(city varchar(15)) RETURNS salary_info AS $$
    SELECT
        MAX(employees.salary) AS min_salary,
        MIN(employees.salary) AS max_salary
    FROM employees
        WHERE employees.city = test_1.city;
$$ LANGUAGE SQL;

    Even if functions are created with parameter names that are distinct from column names in the queried tables, there is no guarantee that someone won’t inadvertently (or maliciously) create a conflicting column in the future.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search