?
Solved

postgres function construction problem

Posted on 2007-10-18
7
Medium Priority
?
2,240 Views
Last Modified: 2012-06-22
i've been creating a function in postgres, as i would like to request  a "select is_super_account(id)"  with and id  
and have a boolean answer to know if the id is  a super account or not.  

in my table of account, if the account_id exist in super_account_id columns, the account is a "super account".
i have start creating a function with pgAdmin, how can i test in the following,
if the select return > 0. in order to return   true or false.
can you help me to complete it ?


CREATE FUNCTION is_super_account(account_id integer) RETURNS boolean AS

'SELECT COUNT(*)  FROM account WHERE super_account_id = account_id'

LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION is_super_account(integer) OWNER TO community_user;


other question:
what is a good return type if i'd like to return complete rows.
how would be a function which receives a table name and two integers representing limits, like 3 and 7 , and that would return
from the 3rd to the 7th row of that table?

thanxs in advance ;o)
0
Comment
Question by:toshi_
  • 3
  • 2
  • 2
7 Comments
 
LVL 10

Accepted Solution

by:
adrpo earned 1000 total points
ID: 20113860
Hi,

CREATE FUNCTION is_super_account(account_id integer) RETURNS boolean AS $PROC$
DECLARE
  nRows integer;
BEGIN
  EXECUTE 'SELECT COUNT(*)  FROM account WHERE super_account_id ='||quote_literal(account_id)
  INTO nRows;
  IF nRows > 0  then return true;  else return false; END IF;
END
$PROC$ LANGUAGE 'plpgsql' VOLATILE;

Cheers,
za-k/
0
 
LVL 10

Expert Comment

by:adrpo
ID: 20113866

Hi again,

About your other question, see here:
http://www.postgresql.org/docs/8.2/interactive/xfunc-sql.html#XFUNC-OUTPUT-PARAMETERS
33.4.5. SQL Functions Returning Sets

Cheers,
za-k/
0
 
LVL 22

Assisted Solution

by:earth man2
earth man2 earned 1000 total points
ID: 20117299
create or replace function is_super_account_id( int ) returns boolean as $$
select exists( select 1 from account where super_account_id = $1 ); $$ language SQL;

create function( IN start  int, IN finish int ) returns setof SOME_TYPE as ) $$
declare
xx some_type;
begin
  return next xx;
  return;
end;
$$ language plpgsql volatile;
0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 

Author Comment

by:toshi_
ID: 20121996
earthman
i could find the - used by adrpo - quote_literal()  in the list i receive from "\df" in my cygwin postgres,  which reconfort me as i know now where to find functions for plgsql
Conversely i couldn't find any reference to the function you use:  "exist()", is there another list of built-in functions?

earthman2:
my question:
 i want a function i can call as following :
table_id(table_name, 100, 200);  // several lines from a table.
i can not succeed with it:
i've been using what you wrote, but i dont know how to replace the table name passed as parameter with the "level%ROWTYPE".
i dont understand either how you stop the return without using "finish" is it implicit with next?  

CREATE OR REPLACE FUNCTION table_lines(IN start int, IN finish int) RETURNS setof text  as $$
 declare
   myrow level%ROWTYPE;
 begin
     return next myrow;
     return;
 end;
 $$ language plpgsql volatile;


 
thanks a lot.
 
0
 
LVL 22

Expert Comment

by:earth man2
ID: 20129351
EXISTS is a standard SQL function.

I gave you a template for you to start writing your own table_lines function.  RETURN NEXT does not pass control back to the calling function, it puts a new returning row on the "stack".

CREATE OR REPLACE FUNCTION table_lines(IN start int, IN finish int) RETURNS setof level as $$ ...
0
 

Author Comment

by:toshi_
ID: 20130263
hi earthman2

i had problemns with my return values, i've been looking for something structured...easy to understant.
so i have found an example at the following page: http://www.postgresql.org/docs/techdocs.17

example :
      declare
      r holder%rowtype;
      begin
         for r in select departmentid, sum(salary) as totalsalary from GetEmployees() group by departmentid loop
      return next r;
     end loop;
     return;
     end
     '
      language 'plpgsql';



here's what i did following the example. (please don't write SQL but plpgsql as i'm trying to learn something. and i dont want to get lost with languages)

i have two tables, account table and user_person table, they have a value in common.

 CREATE TYPE account_view AS (account_id integer, firstname character varying, lastname character varying);

 CREATE OR REPLACE FUNCTION get_all_accounts() RETURNS setof account_view AS
 $PROC$
 declare
    r account_view%rowtype;
 begin
     for r in
         SELECT AC.account_id, UP.firstname, UP.lastname
         FROM account AS AC, user_person as UP
         WHERE AC.resp_person_id = UP.user_person_id
     loop
         return next r;
     end loop;
     return;
 end
 $PROC$
 LANGUAGE 'plpgsql';
 ~
i obviously test the query as follows

"select AC.account_id, UP.firstname, UP.lastname
from account as AC, user_person as UP
WHERE AC.resp_person_id = UP.user_person_id "


call
    select get_all_accounts();

error:
    ERROR: set-valued function called in context that cannot accept a set
    SQL state: 0A000
    Context: PL/pgSQL function "get_all_accounts" line 9 at return next


just for curiosity, i change the r for myrow, i then had a the following error:
error:

alt_st_johann=# \i  get_all_accounts.sql
psql:get_all_accounts.sql:17: ERROR:  loop variable of loop over rows must be a record or row variable or list of scalar va
ables at or near "loop"
LINE 9:   WHERE AC.resp_person_id = UP.user_person_id loop


please, help, i need to have a function which return several lines from a query tables.




0
 

Author Comment

by:toshi_
ID: 20130401
thank you earthman2 and adrpo !!!!!!!!!!

my function works !!!!!!

i was calling her in a wrong way:
     select get_all_accounts();    
instead of
     select * from get_all_accounts():

btw,
why do we have to specify the select, why the call is not just the call to the function, as i was doing by instinct ?????

I'm  happy, i climb the first 10 meters of this 8200 m high montain !!!!
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Best database to use for Maps is PostgreSQL. This is an open source database. Comes as a package with most Linux OS. For more info visit the following site: http://www.postgresql.org/ (http://www.postgresql.org/) This requires some add-o…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question