Calling a function (Stored Procedure) returning a recordset

[Sanjay]

Hi,

I have a postgres function returning a set of records (using RETURN
NEXT). While calling the function like this:

tasklist = func.get_tasklist(engine=engine).execute() # is this the
correct way?

I am getting this error:

SQLError: ('(ProgrammingError) set-valued function called in context
that cannot accept a set\nCONTEXT: PL/pgSQL function "get_tasklist"
line 6 at return next\n', <bound method TaskController.get_tasks of
<gcollab.controllers.task.TaskController object at 0xb742716c>>)
'SELECT get_tasklist()' {}

Am I calling the stored procedure correctly? Need help.

thanks
Sanjay

Here is code of the stored procedure:

CREATE OR REPLACE FUNCTION get_tasklist() RETURNS setof tasklist_t AS $
$
DECLARE
t tasklist_t%rowtype;
BEGIN
FOR t IN SELECT task_id, descr, 'Waiting', remind_at, 'Sanjay'
FROM task
LOOP
RETURN NEXT t;
END LOOP;
RETURN;
END;
$$ LANGUAGE PLPGSQL;

[Michael Bayer]

On May 1, 2007, at 7:06 AM, Sanjay wrote:

>
> Hi,
>
> I have a postgres function returning a set of records (using RETURN
> NEXT). While calling the function like this:
>
> tasklist = func.get_tasklist(engine=engine).execute() # is this the
> correct way?
>

looks fine to me. for the rest of it, im not very familiar with PG
stored procedures.

[Sanjay]

> > tasklist = func.get_tasklist(engine=engine).execute() # is this the
> > correct way?
>
> looks fine to me. for the rest of it, im not very familiar with PG
> stored procedures.

I think SQLAlchemy code is translating to "SELECT get_tasklist()". But
what is actually needed is "SELECT * FROM get_tasklist()".

So, probably the question is, What SQLAlchemy code will translate to
"SELECT * FROM get_tasklist()". Needing suggestions.

thanks
Sanjay

[Sanjay]

> So, probably the question is, What SQLAlchemy code will translate to
> "SELECT * FROM get_tasklist()". Needing suggestions.

Is it like this?:

tasklist = engine.text('SELECT * FROM
get_tasklist()').execute().fetchall()

It returns a list. Any idea how to get a SelectResults, would help a
lot.

thanks
Sanjay

[Michael Bayer]

been meaning to document this

s = select(["*"], from_obj=[func.get_tasklist()], engine=engine)

result = s.execute()

alternately, you can name the columns:

s = select([column('col1'), column('col2')], from_obj=
[func.get_tasklist()], engine=engine)

this select has columns like any other table, i.e. s.c.col1,
s.c.col2, etc. which can be used to embed within subqueries.

[Michael Bayer]

SelectResults is only used with the ORM. youd have to map a class to
your selectable function (the selectable function being created as i
noted in my previous email). also SelectResults is deprecated as
Query includes all of its functionality now.

[Sanjay]

Hi Michael,

Thanks a lot.

> Also SelectResults is deprecated as

> Query includes all of its functionality now.

So nice! Does this mean, in laymans term,

1. I do not have to bother writing "import
sqlalchemy.mods.selectresults"
2. Normal query, whether used raw or through ORM, can supply data to
paginated data grids as efficiently as SelectResults was doing?

thanks
Sanjay

[Sanjay]

> > Also SelectResults is deprecated as
> > Query includes all of its functionality now.
>
> So nice! Does this mean, in laymans term,
>
> 1. I do not have to bother writing "import
> sqlalchemy.mods.selectresults"
> 2. Normal query, whether used raw or through ORM, can supply data to
> paginated data grids as efficiently as SelectResults was doing?

Further findings and queries on SelectResults:

1. Is SelectResults still needed in certain situations:

a. Observed that SelectResults was having a count() method. If we
don't use SelectResults, either we have to query the database for
getting the count manually, or use len(list). Using 'len' may not be
recommended in paginated data grids.

b. Seeing the TurboGears code for 'paginate', it checks for the type
of variable. If it is a list, it just applies len(list)! Does that
mean, we have to explicitly use SelectResults with TurboGears?

2. SelectResults not behaving properly:

I have some code which returns a list although I expect a
SelectResults. Here is the minimal version reproducing that. Can't
guess whether I am doing something wrong or it's a bug. Need help.

from sqlalchemy import *
from sqlalchemy.ext.assignmapper import assign_mapper
from sqlalchemy.ext.sessioncontext import SessionContext
import sqlalchemy.mods.selectresults

context = SessionContext(create_session)
session = context.current

metadata = BoundMetaData('sqlite:///satest', echo=True)

# table definitions
person_table = Table('person', metadata,
Column('person_id', Integer, primary_key=True, autoincrement =
True),
Column('first_name', Unicode(30)),
Column('last_name', Unicode(30)))

metadata.drop_all()
metadata.create_all()

class Person(object):
pass

assign_mapper(context, Person, person_table)

p1 = Person(first_name="Sanjay", last_name="Patel")
p2 = Person(first_name="Ranjan", last_name="Naik")
session.flush()
del p1
del p2
session.clear()
# persons = Person.select_by(person_id=1)
# assert isinstance(persons,
sqlalchemy.ext.selectresults.SelectResults) # OK
persons = Person.select(Person.c.person_id.in_(1))
assert isinstance(persons, sqlalchemy.ext.selectresults.SelectResults)
# Fails!

[Sanjay]

Hi Michael,

The post being too long probably was not that clear to draw your
attention. In summary, it tries to justify the need of SelectResults
still in certain situations. Also, it tries to point out what seems
like a bug in SelectResults. Would need your comments & help on this.

thanks
Sanjay

[Michael Bayer]

On May 14, 2007, at 1:28 AM, Sanjay wrote:

Further findings and queries on SelectResults:

1. Is SelectResults still needed in certain situations:

a. Observed that SelectResults was having a count() method. If we

don't use SelectResults, either we have to query the database for

getting the count manually, or use len(list). Using 'len' may not be

recommended in paginated data grids.

query has a count() method as well.

b. Seeing the TurboGears code for 'paginate', it checks for the type

of variable. If it is a list, it just applies len(list)! Does that

mean, we have to explicitly use SelectResults with TurboGears?

Pylons has taken this issue into account with its own paginage function:

http://pylonshq.com/docs/0.9.5/module-webhelpers.pagination.html

the size of the list is passed separately (which is typically achieved via a single count() call), if not present uses len(list).  TG should follow this example.

this was a small bug that was fixed in trunk a few weeks ago.  SelectResults is deprecated anyway and its easier to use query directly.

[Sanjay]

> >> a. Observed that SelectResults was having a count() method. If we
> >> don't use SelectResults, either we have to query the database for
> >> getting the count manually, or use len(list). Using 'len' may not be
> >> recommended in paginated data grids.
>
> query has a count() method as well.

Then, what is a way to know the count in situations like this?

persons = Person.select_by(surname='Patel')
row_count = persons.count() # produces error as
persons is a list object

> >> b. Seeing the TurboGears code for 'paginate', it checks for the type
> >> of variable. If it is a list, it just applies len(list)! Does that
> >> mean, we have to explicitly use SelectResults with TurboGears?
>
> Pylons has taken this issue into account with its own paginage function:
>
> http://pylonshq.com/docs/0.9.5/module-webhelpers.pagination.html
>
> the size of the list is passed separately (which is typically
> achieved via a single count() call), if not present uses len(list).
> TG should follow this example.

Started some discussion in TG group on this. Is there any plan to drop
SelectResults from SQLAlchemy before TG is taken care, it might create
some problem to user like me.

> this was a small bug that was fixed in trunk a few weeks ago.
> SelectResults is deprecated anyway and its easier to use query directly.

A near future release covers this?

thanks
Sanjay

[Eric Ongerth]

> > query has a count() method as well.
>
> Then, what is a way to know the count in situations like this?
>
> persons = Person.select_by(surname='Patel')
> row_count = persons.count() # produces error as
> persons is a list object
>

see: http://www.sqlalchemy.org/docs/sqlalchemy_orm.html#docstrings_sqlalchemy.orm_Query
-- look for the method count_by().

[Michael Bayer]

On May 15, 2007, at 2:45 AM, Sanjay wrote:

>
>>>> a. Observed that SelectResults was having a count() method. If we
>>>> don't use SelectResults, either we have to query the database for
>>>> getting the count manually, or use len(list). Using 'len' may
>>>> not be
>>>> recommended in paginated data grids.
>>
>> query has a count() method as well.
>
> Then, what is a way to know the count in situations like this?
>
> persons = Person.select_by(surname='Patel')
> row_count = persons.count() # produces error as
> persons is a list object
>

session.query(Person).count(surname='Patel')

>>>> b. Seeing the TurboGears code for 'paginate', it checks for the
>>>> type
>>>> of variable. If it is a list, it just applies len(list)! Does that
>>>> mean, we have to explicitly use SelectResults with TurboGears?
>>
>> Pylons has taken this issue into account with its own paginage
>> function:
>>
>> http://pylonshq.com/docs/0.9.5/module-webhelpers.pagination.html
>>
>> the size of the list is passed separately (which is typically
>> achieved via a single count() call), if not present uses len(list).
>> TG should follow this example.
>
> Started some discussion in TG group on this. Is there any plan to drop
> SelectResults from SQLAlchemy before TG is taken care, it might create
> some problem to user like me.

its already deprecated. the module itself will probably not be
"dropped" for quite some time.

>
>> this was a small bug that was fixed in trunk a few weeks ago.
>> SelectResults is deprecated anyway and its easier to use query
>> directly.
>
> A near future release covers this?

the SR bug fix will be in 0.3.8 / 0.4.0 whichever is first. trunk is
available right now.

[Michael Bayer]

On May 15, 2007, at 11:08 AM, Michael Bayer wrote:

>
> session.query(Person).count(surname='Patel')
>

correction, session.query(Person).count_by(surname='Patel')

[Sanjay]

Thanks a lot, Michael and Eric! This long thread has been quite useful
to me.

Sanjay