Manager.raw() returns an empty RawQuerySet

Using Django
1

I’ve written a pair of SQL functions that work well when I run them directly from SQL scripts and now I’m trying to run them from Manager.raw(). The first runs fine with both a standalone SQL script and with Manager.raw(). In this case I have an instance method where I pass self.id to my SQL function which then returns a temporary table that Manager.raw() consumes:

    def get_store_products_sql(self) -> list['Product']:
        return Product.objects.raw(
                                     '''SELECT  prod_id AS id,
                                                prod_name,
                                                prod_price
                                          FROM  get_store_products(%s)''',
                                     (self.id,)
                                  )

In the second case have a “regular” method where I pass a string argument to a SQL function which expects a VARCHAR. The SQL function should then return a temporary table for Manager.raw() to consume:

    def get_products_by_color_sql(color) -> list['Product']:
        return Product.objects.raw(
                                     '''SELECT  prod_id AS id,
                                                prod_name,
                                                prod_price
                                          FROM  get_products_by_color(%s)''',
                                     (color,)
                                  )

This SELECT statement works when I run it in a SQL script but it fails pretty silently when I run it with Manager.raw() as shown above. It returns a RawQuerySelect iterator object with a correct params value. But when I try to iterate it, it has a length of 0. I’ve tried stepping through this with pdb, but I can’t find anything. I know that I need to return an id field from the SELECT statement and for that I’m taking the same approach as in the first example.

I’ve put RAISE statements in my get_products_by_color() function and they aren’t getting called, so I doubt the problem is with my SQL function.

I know this is a bare example, but since this a demo for now I can also put up a full repo on GitHub.

2

Is this method (get_products_by_color_sql) a method within a class?

If so, you didn’t include self as the first parameter being passed in to the function.

3

Yes, it’s a method in a Model class, but not an instance method. So I would need to put it in a Manager class and I just tried that with the same result.

class ProductManager(Manager):
    def get_products_by_color_sql(self, color) -> list['Product']:
        breakpoint()
        return Product.objects.raw(
                                     raw_query  = '''SELECT  prod_id AS id,
                                                             prod_name,
                                                             prod_price
                                                       FROM  get_products_by_color(%s)''',
                                     params     = (color,)
                                  )
4

I finally had the sense to run this outside of the Model class with manage.py and got the same result. So this isn’t a Model method problem.

${MY_PROJ}/manage.py shell
from myproj.myapp.models import Product

color = "green"

products = Product.objects.raw(
                            raw_query  = '''SELECT  prod_id AS id,
                                                    prod_name,
                                                    prod_price
                                              FROM  get_products_by_color(%s)''',
                              params     = (color,)
                          )

for prod in products:
    print(prod.name)

In [4]: products
Out[4]: 
    <RawQuerySet: SELECT  prod_id AS id,
                                                     prod_name,
                                                     prod_price
                                               FROM  get_products_by_color(green)>

In [5]: len(products)
Out [5]: 0
5

Have you verified that get_products_by_color works as intended on the server?

I’m wondering if, due to the parameters being passed not being part of a where clause, the normal parameter handling isn’t working as it should in this case.

I’d verify that the query being generated is what it needs to be syntactically.

Try this from within the shell after executing your query:

from django.db import connection
print(connection.queries)
6

Here’s the get_products_by_color() function. As you can see, the WHERE clause is inside the function mostly because I wanted a toy function to work with Manager.raw() before I did anything more complicated. I’ve definitely tested my PL/pgSQL functions as standalone scripts and they run fine.

CREATE OR REPLACE FUNCTION
  get_products_by_color(
    mycolor  VARCHAR
  )
RETURNS   TABLE(
            prod_id     INTEGER,
            prod_name   VARCHAR,
            prod_price  FLOAT
          )
LANGUAGE  plpgsql
AS        $FUNC_BODY$
BEGIN
  RAISE NOTICE 'mycolor:  %', mycolor;
  RETURN QUERY
    -- https://dba.stackexchange.com/a/234047/78978
    SELECT   id, name, price
      FROM   myapp_product
      WHERE  (metadata -> 'color')#>>'{}' = mycolor;
END
$FUNC_BODY$;
7

Fixed it. It was a problem with my quoting in my Django fixtures.