0

I'd like to call a function (that returns a single string) as a field of a SELECT statement. This is my function:

CREATE or replace FUNCTION getData(text) RETURNS text
    AS $$ (select myField from myTable) $$ 
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;

Of course select myField from myTable is more complicated, but returns a single string (text). There is logic in the select statement in the function that cannot be implemented (at least I can't) in a join where I am going to use it. The logic is: join some tables and order by multiple fields and return the first record.

Then I would like to call the function in this way:

select myField1,
getData(myField1) as computated_field
from anotherTable

so that the computatedField is easily fetched instead of joining (I need only one record for each row) and with JOIN I would join many records, and I only need one.

All I get is:

ERROR: missing FROM-clause entry for table "anotherTable"

Is there another way to do this? Or is it that this is not the way to use functions and there must be another instrument for it?

Thanks in advance

morphineglelly
  • 107
  • 7
  • I think you're looking for dynamic sql. – shawnt00 Jun 27 '22 at 17:40
  • 1
    A JOIN is most likely still faster then this extremely slow row-by-row processing that you impose through the function. Besides: the function's query will read the value of an undefined rows from the table –  Jun 27 '22 at 18:11

1 Answers1

0

I figured it out. That is not possible with function as I supposed, but it should have been obvious because it is not a programming language.

I left-joined the table two times on different conditions and coalesced the results.

Another version would have been to use a Select as a field with an EXISTS condition, that was really weird to see.

morphineglelly
  • 107
  • 7