Postgresql - How to set variable value in function

Question

I'm trying to set value of a variable based on case statement and return the data as a table in Postgres function.

In the case statement I'm trying to do bitwise operation on options field

 CREATE OR REPLACE FUNCTION get_ticket_types()
  RETURNS TABLE (
    ticket_type   text   
  ,sale_type text) AS
$func$

declare sale_type text;
BEGIN


   RETURN QUERY

   select name::text as ticket_type, 
(case when (options & 1) > 0 then sale_type = concat(sale_type, 'POS ')
when (options & 2) > 0 then sale_type = concat(sale_type, 'Internet Jetstar ') 
when (options & 4) > 0 then  sale_type = concat(sale_type, 'Internet ')
when (options & 64) > 0 then  sale_type = concat(sale_type, 'Internet FAPAS ')
when (options & 128) > 0 then  sale_type = concat(sale_type, 'Internet Amadeus ')
when (options & 8) > 0 then sale_type = concat(sale_type, 'Agent ')
when (options & 16) > 0 then  sale_type = concat(sale_type, 'Kiosk-Credit ')
when (options & 32) > 0 then  sale_type = concat(sale_type, 'Kiosk-Cash ')
when (options & 32768) > 0 then  sale_type = concat(sale_type, 'Preprinted ')
 end )as sale_type

  from skybus_tickettype
 order by name asc;

END
$func$  LANGUAGE plpgsql;

This is not working. Can anybody tell me what is wrong in this ?

It shows the following error

ERROR: structure of query does not match function result type DETAIL: Returned type boolean does not match expected type text in column 2. CONTEXT: PL/pgSQL function get_ticket_types() line 7 at RETURN QUERY ********** Error **********

klin · Accepted Answer · 2017-08-29 07:33:26Z

Create a text function to get concatenated type names from options:

create or replace function get_sale_type(options int)
returns text language plpgsql as $$
declare
    sale_type text = '';
begin
    if options & 1 > 0 then sale_type:= concat(sale_type, 'POS '); end if;
    if options & 2 > 0 then sale_type:= concat(sale_type, 'Internet Jetstar '); end if; 
    if options & 4 > 0 then sale_type:= concat(sale_type, 'Internet '); end if;
    if options & 8 > 0 then sale_type:= concat(sale_type, 'Agent '); end if;
    if options & 16 > 0 then sale_type:= concat(sale_type, 'Kiosk-Credit '); end if;
    if options & 32 > 0 then sale_type:= concat(sale_type, 'Kiosk-Cash '); end if;
    if options & 64 > 0 then sale_type:= concat(sale_type, 'Internet FAPAS '); end if;
    if options & 128 > 0 then sale_type:= concat(sale_type, 'Internet Amadeus '); end if;
    if options & 32768 > 0 then sale_type:= concat(sale_type, 'Preprinted '); end if;
    return sale_type;
end $$;

and use it in this way:

select 
    name::text as ticket_type, 
    get_sale_type(options) as sale_type
from skybus_tickettype
order by name asc;

Anar Salimkhanov · Accepted Answer · 2023-08-30 07:00:56Z

0

If anyone is getting error ERROR: syntax error at or near "text", make sure that the function language is plpgsql.

CREATE OR REPLACE FUNCTION public.test()
    RETURNS text
    LANGUAGE plpgsql
AS $$
declare
    some_variable text := 1;
BEGIN
...

Had the same problem

answered Aug 30, 2023 at 7:00

Anar Salimkhanov

84712 silver badges15 bronze badges

Collectives™ on Stack Overflow

Postgresql - How to set variable value in function

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related