[SQL] how to create a multi columns return function ?

[jclaudio]

Hi

I'm moving databases from sybase to postgres.
But I have difficulties in creating a postgres equivalent to the sybase stored procedures...

Apparently, Postgres functions should work, but the syb stored procedures get only one parameter and return several colums

Here's the code I wrote in postgresql :

create function function_name( int ) returns text
AS ' SELECT column1, column2, column3,...,column15
FROM table_name
WHERE colum1 = $1 AND column5 = \'specific value\' AND column8 = \'specific_value2 \' '
LANGUAGE 'SQL';

and I get the message error : returns multi columns

I'm wondering too if It's possible to create a view with a parameter if functions don't work.

Has anybody faced the same problem ?

I need help

thanks

[SQL] problem with function trigger

[jclaudio]

Hi I'm trying to update a table column with a pl/pgsql function and a trigger. 
But I didn't managed to make it work so far.

Here's my  function code :

CREATE FUNCTION public.calcul_impact() RETURNS opaque AS '

DECLARE 
id_line integer;
quantity integer;
single_price real; 
total_cost real; 
amort integer;
month integer;
impact real;

BEGIN 

SELECT INTO id_line id_line_table FROM table WHERE id_line_table = NEW.id_line;
SELECT INTO single_price single_price_previ FROM table WHERE id_line_table = NEW.id_line;
SELECT INTO total_cost total_cost_previ FROM table WHERE id_line_table = NEW.id_line;
SELECT INTO quantity quantity_previ FROM table WHERE id_line_table = NEW.id_line;
SELECT INTO amort amortis FROM table WHERE id_line_table = NEW.id_line;
SELECT INTO month month_previ FROM table WHERE id_line_table = NEW.id_line;
SELECT INTO impact impact_previ FROM table WHERE id_line_table = NEW.id_line;

IF(quantity IS NULL OR single_price IS NULL) THEN impact:= 0; 
ELSE IF(quantity >= 12) THEN impact:= (total_cost / amort); 
     ELSE IF(quantity < 12 AND single_price <= 500) THEN impact:= total_cost; 
          ELSE IF(quantity < 12 AND single_price > 500) THEN impact:= ((12 - month)*(total_cost/(amort*12))); 
               END IF;
          END IF;
     END IF;
END IF;

IF (TG_OP =''INSERT'' OR TG_OP=''UPDATE'') THEN 
        UPDATE table SET impact_previ = impact WHERE id_line_table = NEW.id_line; 
END IF; 

RETURN NEW; 

END;

' LANGUAGE 'plpgsql';

CREATE TRIGGER add_impact_previ BEFORE INSERT OR UPDATE ON public.budget FOR EACH ROW EXECUTE PROCEDURE calcul_impact();

I always get the error :

Error SQL :
ERROR:  record "new" has no field named "id_ligne"

Has anyone an idea about what's wrong ?

thanks for answering me

[SQL] problem with function trigger

[jclaudio]

Hi I'm trying to update a table column with a pl/pgsql function and a trigger. 
But I didn't managed to make it work so far.

Here's my  function code :

CREATE FUNCTION public.calcul_impact() RETURNS opaque AS '

DECLARE 
id_line integer;
quantity integer;
single_price real; 
total_cost real; 
amort integer;
month integer;
impact real;

BEGIN 

SELECT INTO id_line id_line_table FROM table WHERE id_line_table = NEW.id_line;
SELECT INTO single_price single_price_previ FROM table WHERE id_line_table = NEW.id_line;
SELECT INTO total_cost total_cost_previ FROM table WHERE id_line_table = NEW.id_line;
SELECT INTO quantity quantity_previ FROM table WHERE id_line_table = NEW.id_line;
SELECT INTO amort amortis FROM table WHERE id_line_table = NEW.id_line;
SELECT INTO month month_previ FROM table WHERE id_line_table = NEW.id_line;
SELECT INTO impact impact_previ FROM table WHERE id_line_table = NEW.id_line;

IF(quantity IS NULL OR single_price IS NULL) THEN impact:= 0; 
ELSE IF(quantity >= 12) THEN impact:= (total_cost / amort); 
     ELSE IF(quantity < 12 AND single_price <= 500) THEN impact:= total_cost; 
          ELSE IF(quantity < 12 AND single_price > 500) THEN impact:= ((12 - month)*(total_cost/(amort*12))); 
               END IF;
          END IF;
     END IF;
END IF;

IF (TG_OP =''INSERT'' OR TG_OP=''UPDATE'') THEN 
        UPDATE table SET impact_previ = impact WHERE id_line_table = NEW.id_line; 
END IF; 

RETURN NEW; 

END;

' LANGUAGE 'plpgsql';

CREATE TRIGGER add_impact_previ BEFORE INSERT OR UPDATE ON public.budget FOR EACH ROW EXECUTE PROCEDURE calcul_impact();

I always get the error :

Error SQL :
ERROR:  record "new" has no field named "id_ligne"

Has anyone an idea about what's wrong ?

thanks for answering me