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

pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed

PostgreSQL Function: how to know the number of the returned results of the Query
3+ messages / 3 participants
[nested] [flat]

* PostgreSQL Function: how to know the number of the returned results of the Query
@ 2009-09-01 00:53 bilal ghayyad <bilmar_gh@yahoo.com>
  2009-09-01 05:31 ` Re: PostgreSQL Function: how to know the number of the returned results of the Query Harun Bolat <harun.bolat@netsim.net>
  2009-09-01 05:35 ` Re: PostgreSQL Function: how to know the number of the returned results of the Query Pavel Stehule <pavel.stehule@gmail.com>
  0 siblings, 2 replies; 3+ messages in thread

From: bilal ghayyad @ 2009-09-01 00:53 UTC (permalink / raw)
  To: pgsql-sql

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

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: PostgreSQL Function: how to know the number of the returned results of the Query
  2009-09-01 00:53 PostgreSQL Function: how to know the number of the returned results of the Query bilal ghayyad <bilmar_gh@yahoo.com>
@ 2009-09-01 05:31 ` Harun Bolat <harun.bolat@netsim.net>
  1 sibling, 0 replies; 3+ messages in thread

From: Harun Bolat @ 2009-09-01 05:31 UTC (permalink / raw)
  To: bilal ghayyad <bilmar_gh@yahoo.com>; 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
         rate numberic(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 IF
 END
 $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 




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: PostgreSQL Function: how to know the number of the returned results of the Query
  2009-09-01 00:53 PostgreSQL Function: how to know the number of the returned results of the Query bilal ghayyad <bilmar_gh@yahoo.com>
@ 2009-09-01 05:35 ` Pavel Stehule <pavel.stehule@gmail.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Pavel Stehule @ 2009-09-01 05:35 UTC (permalink / raw)
  To: bilal ghayyad <bilmar_gh@yahoo.com>; +Cc: pgsql-sql

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. 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
>



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

end of thread, other threads:[~2009-09-01 05:35 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-09-01 00:53 PostgreSQL Function: how to know the number of the returned results of the Query bilal ghayyad <bilmar_gh@yahoo.com>
2009-09-01 05:31 ` Harun Bolat <harun.bolat@netsim.net>
2009-09-01 05:35 ` Pavel Stehule <pavel.stehule@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox