How to pass a Tables Column into a plpgsql Functiion while performing a SELECT... statement-CodePudding

I googled but everyone was asking for how to pass tables or how to use the return result into a Function; I want to do neither. I simply want to take the value of a Column (lets assume col2 below is of the text datatype) of a table, and pass that data into a Function, so I can manipulate the data, but in the SELECT... statement itself, i.e.

SELECT t.col1, "myCustomFunction"(t.col2)
FROM tbl t
WHERE t.col1 = 'someCondition';


CREATE OR REPLACE FUNCTION myCustomFunction(myArg text) 
RETURNS text AS $$
DECLARE 

BEGIN
     
      RETURN UPPER(myArg);

END
$$ LANGUAGE plpgsql;

... So if myCustomerFunction()'s job was capitalize letters (its not, just an example), the output would be the table with col2 data all capitalized.

Is this possible? I supposed it would be no different than embedding a CASE expression there, which I know works, and a Function returns a result, so I assumed it would be the same, but I am getting SQL Error

CodePudding user response：

You cannot to pass named column to some function and you cannot to return this named column like table with this column. The table is set of rows, and almost all processing in Postgres is based on rows processing. Usually you need to hold only data of one row in memory, so you can process much bigger dataset than is your memory.

Inside PL/pgSQL function you have not informations about outer. You can get just data of scalar types, arrays of scalars, or composite or arrays of composites (or ranges and multiranges - this special kind of composite and array of composite). Nothing else.

Theoretically you can aggregate data in one column to array, and later you can expand this array to table. But these operations are memory expensive and can be slow. You need it only in few cases (like computing of median function), but it is slow, and there is risk of out of memory exception.

CodePudding user response：

When object names are not doubled quoted Postgres processes then internally as lower case. When doubled quoted the are processed exactly as quoted. The thing is these may not be the same. You defined the function as FUNCTION myCustomFunction(myArg text) Not doubled quoted, but attempt to call it via "myCustomFunction"(t.col2). Unfortunately myCustomFunction processed as mycustomfunction but "myCustomFunction" is processed exactly as it appears. Those are NOT the same. Either change your select to:

SELECT t.col1,myCustomFunction(t.col2)
FROM tbl t
WHERE t.col1 = 'someCondition';

or change the function definition to:

CREATE OR REPLACE FUNCTION "myCustomFunction"(myArg text) 
RETURNS text AS $$
DECLARE 
BEGIN    
      RETURN UPPER(myArg);
END
$$ LANGUAGE plpgsql;