I need an expert Postgresql.

[Seti Volkylany]

I am try to conver from numberic to string a result of annotated field (created by a annotation).

I am wrote my Func as next:

class ToStr(Func):
    """


    """


    function = 'CAST'
    template = '%(function)s(%(expressions)s as TEXT)'




class ToChar(Func):
    """


    """


    function = 'CAST'
    template = '%(function)s(%(expressions)s as VARCHAR)'

I have next code for queryset of model:

class SolutionQuerySet(models.QuerySet):
    """
    QuerySet for solutions.
    """


    def solutions_with_scopes(self):
        """Added for each solution new field 'scope' where storage her scope."""


        self = self.annotate(scope=models.Sum(
            models.Case(
                models.When(opinions__is_useful=True, then=1),
                models.When(opinions__is_useful=False, then=-1),
                output_field=models.IntegerField()
            )
        ))
        self = self.annotate(scope=models.functions.Coalesce('scope', 0))
        return self


    def solutions_with_displayed_scopes(self):
        """Convert scope of solution to string and return with sign of number:
        if 0 return 0, if 1 return +1, if -1 return -1."""


        self = self.solutions_with_scopes()
        self = self.annotate(s=ToStr('scope'))
        self = self.annotate(s1=ToChar('scope'))
        # self = self.annotate(displayed_scope=models.Case(
        #     models.When(scope__exact=0, then=models.Value('0')),
        #     models.When(scope__lt=0, then=models.Value('-')),
        #     models.When(scope__gt=0, then=models.Value('+')),
        #     output_field=models.CharField(),
        # ))
        return self

This converting working with SlugField and UUIDField, however not with IntegerField (created by annotate()):

Worked with SlugField:

    def solutions_with_displayed_scopes(self):
        """Convert scope of solution to string and return with sign of number:
        if 0 return 0, if 1 return +1, if -1 return -1."""


        self = self.solutions_with_scopes()
        self = self.annotate(s=ToStr('slug'))
        self = self.annotate(s1=ToChar('slug'))

In [2]: Solution.objects.solutions_with_displayed_scopes().values('s', 's1')[0]
Out[2]: {'s': 'how-fix-problem-on-heroku', 's1': 'how-fix-problem-on-heroku'}

Worked with UUIDField:

    def solutions_with_displayed_scopes(self):
        """Convert scope of solution to string and return with sign of number:
        if 0 return 0, if 1 return +1, if -1 return -1."""


        self = self.solutions_with_scopes()
        self = self.annotate(s=ToStr('id'))
        self = self.annotate(s1=ToChar('id'))

In [1]: Solution.objects.solutions_with_displayed_scopes().values('s', 's1')[0]
Out[1]: 
{'s': '9f9beadd-be37-4475-9e11-890172987f80',
 's1': '9f9beadd-be37-4475-9e11-890172987f80'}

Not worked with IntegerField:

    def solutions_with_displayed_scopes(self):
        """Convert scope of solution to string and return with sign of number:
        if 0 return 0, if 1 return +1, if -1 return -1."""


        self = self.solutions_with_scopes()
        self = self.annotate(s=ToStr('scope'))
        self = self.annotate(s1=ToChar('scope'))

In [1]: Solution.objects.solutions_with_displayed_scopes().values('s', 's1')[0]
Out[1]: {'s': -2, 's1': -2}

Must be:

In [1]: Solution.objects.solutions_with_displayed_scopes().values('s', 's1')[0]
Out[1]: {'s': '-2', 's1': '-2'}

Can anyone explain, why Postgres have this behaivor?

[Simon Charette]

Hi Seti,

I suppose this has to do with your Func subclasses not defining an `output_field`.

You might want to have a look at the Cast function's implementation which will
be part of Django 1.10 for more details[1].

It should allow you to do the following:

self.annotate(s=Cast('scoppe', models.TextField))

Cheers,
Simon

[1] https://docs.djangoproject.com/en/1.10/ref/models/database-functions/