Thread: PostgreSQL Function: how to know the number of the returned results of the Query

Hi List;

I am building a function in the SQL (PostgreSQL), so I will be able to call this function using a SELECT query. Inside
thebody of this function, I was need to do the following but not able to know how:
 

I have a SELECT statement (inside the function script itself), and I need to know the number of the returned rows of
thatselect query, if it is one row or 2 or 3 , ... How? In which paramter I will be able to check this?
 

For example, I have the following function:

CREATE OR REPLACE FUNCTION get_credit_time(text, text) RETURNS integer AS
$BODY$
DECLARE
BEGIN        rate numberic(9,4);SELECT rate from voiptariff where id= 9;        IF num_rows ==1 THEN   -- As example,
butI am asking how to do it?        .............        ELSE        .............        END IF
 
END
$BODY$ LANGUAGE 'sql' IMMUTABLE COST 100;
ALTER FUNCTION get_bool(text) OWNER TO gkradius;

In this function, I need to check the number of returned rows of the statement: SELECT rate from voiptariff where id=
9;because based on it I am going to build if statment, How?
 

Any help?

Regards
Bilal

     

Hello

look on PERFORM and GET DIAGNOSTICS statements

http://www.postgresql.org/docs/8.2/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-NORESULT
http://www.postgresql.org/docs/8.2/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS

regards
Pavel Stehule

2009/9/1 bilal ghayyad <bilmar_gh@yahoo.com>:
> Hi List;
>
> I am building a function in the SQL (PostgreSQL), so I will be able to call this function using a SELECT query.
Insidethe body of this function, I was need to do the following but not able to know how: 
>
> I have a SELECT statement (inside the function script itself), and I need to know the number of the returned rows of
thatselect query, if it is one row or 2 or 3 , ... How? In which paramter I will be able to check this? 
>
> For example, I have the following function:
>
> CREATE OR REPLACE FUNCTION get_credit_time(text, text)
>  RETURNS integer AS
> $BODY$
> DECLARE
> BEGIN
>         rate numberic(9,4);
>        SELECT rate from voiptariff where id= 9;
>         IF num_rows ==1 THEN   -- As example, but I am asking how to do it?
>         .............
>         ELSE
>         .............
>         END IF
> END
> $BODY$
>  LANGUAGE 'sql' IMMUTABLE
>  COST 100;
> ALTER FUNCTION get_bool(text) OWNER TO gkradius;
>
> In this function, I need to check the number of returned rows of the statement: SELECT rate from voiptariff where id=
9;because based on it I am going to build if statment, How? 
>
> Any help?
>
> Regards
> Bilal
>
>
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>

count aggregate function can be use like this.

"SELECT count(rate) into row_count from voiptariff where id= 9;"


CREATE OR REPLACE FUNCTION get_credit_time(text, text) RETURNS integer AS$BODY$DECLARE row_count integer;BEGIN
ratenumberic(9,4);SELECT count(rate) into row_count from voiptariff where id= 9;        IF row_count =1 THEN   -- As
example,but I am asking how to do it?        .............        ELSE        .............        END IFEND$BODY$
LANGUAGE'sql' IMMUTABLE COST 100;ALTER FUNCTION get_bool(text) OWNER TO gkradius;
 


----- Original Message ----- 
From: "bilal ghayyad" <bilmar_gh@yahoo.com>
To: <pgsql-sql@postgresql.org>
Sent: Tuesday, September 01, 2009 3:53 AM
Subject: [SQL] PostgreSQL Function: how to know the number of the returned 
results of the Query


> Hi List;
>
> I am building a function in the SQL (PostgreSQL), so I will be able to 
> call this function using a SELECT query. Inside the body of this function, 
> I was need to do the following but not able to know how:
>
> I have a SELECT statement (inside the function script itself), and I need 
> to know the number of the returned rows of that select query, if it is one 
> row or 2 or 3 , ... How? In which paramter I will be able to check this?
>
> For example, I have the following function:
>
> CREATE OR REPLACE FUNCTION get_credit_time(text, text)
>  RETURNS integer AS
> $BODY$
> DECLARE
> BEGIN
>         rate numberic(9,4);
> SELECT rate from voiptariff where id= 9;
>         IF num_rows ==1 THEN   -- As example, but I am asking how to do 
> it?
>         .............
>         ELSE
>         .............
>         END IF
> END
> $BODY$
>  LANGUAGE 'sql' IMMUTABLE
>  COST 100;
> ALTER FUNCTION get_bool(text) OWNER TO gkradius;
>
> In this function, I need to check the number of returned rows of the 
> statement: SELECT rate from voiptariff where id= 9; because based on it I 
> am going to build if statment, How?
>
> Any help?
>
> Regards
> Bilal
>
>
>
>
> -- 
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql