Sql error 42601 ошибка return and sql tuple descriptions are incompatible

Thread: Return and sql tuple descriptions are incompatible

Return and sql tuple descriptions are incompatible

I have the following crosstab command :

Select * from crosstab($$select produkid, warehouseid,onhand from vwtest order by 1,2$$)

as t (produkid VARCHAR, warehouseid integer)

CREATE OR REPLACE VIEW public.vwtest (

В В В warehouseid,

В В В dt.warehouseid,

В В В sum(dt.awal + dt.ttlmsk — dt.ttlklr) AS onhand

В В В SELECT tblstockawal.kodebarang AS produkid,

В В В В В В В В В В В В В В В В В В В tblstockawal.warehouseid,

В В В В В В В В В В В В В В В В В В В sum(tblstockawal.qty) AS awal,

В В В В В В В В В В В В В В В В В В В 0 AS ttlmsk,

В В В В В В В В В В В В В В В В В В В 0 AS ttlklr

В В В FROM tblstockawal

В В В GROUP BY tblstockawal.kodebarang, tblstockawal.warehouseid

В В В SELECT tbltransaksi.kodebarang AS produkid,

В В В В В В В В В В В В В В В В В В В tbltransaksi.warehouseid,

В В В В В В В В В В В В В В В В В В В 0 AS awal,

В В В В В В В В В В В В В В В В В В В sum(tbltransaksi.masuk) AS ttlmsk,

В В В В В В В В В В В В В В В В В В В sum(tbltransaksi.keluar) AS ttlklr

В В В FROM tbltransaksi

В В В GROUP BY tbltransaksi.kodebarang, tbltransaksi.warehouseid

GROUP BY dt.produkid, dt.warehouseid;

The crosstab command didn’t work with error : Return and sql tuple descriptions are incompatible.

I have tried to change productid type to text and warehouseid to float8 and the problem still not solve.

Источник

Ошибка postgres Crosstab о кортеже, но возвращаемый тип соответствия столбцов?

Я продолжаю получать эту ошибку, но типы совпадают, поскольку я проверял с помощью pg_typeof для столбцов. Они вернули текст и bigint.

Вот определение тестовой таблицы

И запрос, возвращающий ошибку. Я ищу в Google и StackOverflow, и я в тупике.

Любые идеи относительно того, почему я получаю сообщение об ошибке?

Возможно, вы не поняли суть crosstab .
(reader_name text,metric_key text,metric_value bigint) — это вход кросс-таблицы. Это определяется:

1. имя строки (возвращается в 1-м столбце кросс-таблицы)
2. название категории (не возвращается, но используется для упорядочивания категорий)
3. значение категории (возвращается, начиная со 2-го столбца кросс-таблицы)

Ожидается, что вы укажете вывод, который в вашем случае:
(reader_name text,metric_value1 bigint,metric_value2 bigint,metric_value3 bigint)

Если вы не добавите предложение WHERE , чтобы гарантировать, что внутренним запросом не будет возвращено не более N значений, по мере роста вашей базы данных потребуется добавлять больше значений.

Другие вопросы по теме

Похожие вопросы

Находите ответы на сложные технические вопросы по программированию, с которыми сталкиваются инженеры по всему миру в своей ежедневной практике на сайте RedDeveloper.

Источник

PostgreSQL – SQL state: 42601 syntax error

Posted By: Anonymous

I would like to know how to use a dynamic query inside a function. I’ve tried lots of ways, however, when I try to compile my function a message SQL 42601 is displayed.

The code that I use:

Error message I receive:

What is wrong? How can I solve this problem?

Solution

Your function would work like this:

You cannot mix plain and dynamic SQL the way you tried to do it. The whole statement is either all dynamic or all plain SQL. So I am building one dynamic statement to make this work. You may be interested in the chapter about executing dynamic commands in the manual.

The aggregate function count() returns bigint , but you had rowcount defined as integer , so you need an explicit cast ::int to make this work

I use dollar quoting to avoid quoting hell.

However, is this supposed to be a honeypot for SQL injection attacks or are you seriously going to use it? For your very private and secure use, it might be ok-ish – though I wouldn’t even trust myself with a function like that. If there is any possible access for untrusted users, such a function is a loaded footgun. It’s impossible to
make this secure.

Craig (a sworn enemy of SQL injection!) might get a light stroke, when he sees what you forged from his piece of code in the answer to your preceding question. 🙂

The query itself seems rather odd, btw. But that’s beside the point here.

Answered By: Anonymous

Related Articles

Disclaimer: This content is shared under creative common license cc-by-sa 3.0. It is generated from StackExchange Website Network.

Источник

Syntax errors are quite common while coding.

But, things go for a toss when it results in website errors.

PostgreSQL error 42601 also occurs due to syntax errors in the database queries.

At Bobcares, we often get requests from PostgreSQL users to fix errors as part of our Server Management Services.

Today, let’s check PostgreSQL error in detail and see how our Support Engineers fix it for the customers.

What causes error 42601 in PostgreSQL?

PostgreSQL is an advanced database engine. It is popular for its extensive features and ability to handle complex database situations.

Applications like Instagram, Facebook, Apple, etc rely on the PostgreSQL database.

But what causes error 42601?

PostgreSQL error codes consist of five characters. The first two characters denote the class of errors. And the remaining three characters indicate a specific condition within that class.

Here, 42 in 42601 represent the class “Syntax Error or Access Rule Violation“.

In short, this error mainly occurs due to the syntax errors in the queries executed. A typical error shows up as:

Here, the syntax error has occurred in position 119 near the value “parents” in the query.

How we fix the error?

Now let’s see how our PostgreSQL engineers resolve this error efficiently.

Recently, one of our customers contacted us with this error. He tried to execute the following code,

CREATE OR REPLACE FUNCTION prc_tst_bulk(sql text)
RETURNS TABLE (name text, rowcount integer) AS
$$
BEGIN
WITH m_ty_person AS (return query execute sql)
select name, count(*) from m_ty_person where name like '%a%' group by name
union
select name, count(*) from m_ty_person where gender = 1 group by name;
END
$$ LANGUAGE plpgsql;

But, this ended up in PostgreSQL error 42601. And he got the following error message,

ERROR: syntax error at or near "return"
LINE 5: WITH m_ty_person AS (return query execute sql)

Our PostgreSQL Engineers checked the issue and found out the syntax error. The statement in Line 5 was a mix of plain and dynamic SQL. In general, the PostgreSQL query should be either fully dynamic or plain. Therefore, we changed the code as,

RETURN QUERY EXECUTE '
WITH m_ty_person AS (' || sql || $x$)
SELECT name, count(*)::int FROM m_ty_person WHERE name LIKE '%a%' GROUP BY name
UNION
SELECT name, count(*)::int FROM m_ty_person WHERE gender = 1 GROUP BY name$x$;

This resolved the error 42601, and the code worked fine.

[Need more assistance to solve PostgreSQL error 42601?- We’ll help you.]

Conclusion

In short, PostgreSQL error 42601 occurs due to the syntax errors in the code. Today, in this write-up, we have discussed how our Support Engineers fixed this error for our customers.

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;

Вопрос:

ID  CLASS   VALUE
1   NHB    700905.7243
1   HBW    164216.1311
1   HBO    700905.7243
2   NHB    146023.3792
2   HBW    89543.2972
2   HBO    82152.072
3   NHB    1409818.328
3   HBW    220430.7922
3   HBO    323512.9391
4   NHB    48711.3814
4   HBW    163385.1575
4   HBO    363352.3441

ID     HBO             HBW              NHB
1   700905.7243    164216.1311      700905.7243
2   82152.072      89543.2972       146023.3792
3   323512.9391    220430.7922      1409818.328
4   363352.3441    163385.1575      48711.3814

-- CREATE EXTENSION tablefunc;

SELECT *
FROM  CROSSTAB
(
'SELECT _tlfd.id,
_tlfd."class",
_tlfd."value"
FROM public._tlfd
WHERE _tlfd."class" = ''HBW'' or _tlfd."class" = ''HBO'' or _tlfd."class" = ''NHB''
ORDER BY 1,2'
)
AS
(
"class" int,
"HBW" text,
"HBO" text,
"NHB" text,
--"Purpose" varchar,
"value" double precision
);

Лучший ответ:

SELECT *
    FROM  crosstab (
        $$SELECT id, class, "value"
         FROM   _tlfd
         WHERE  class = ANY ('{HBW, HBO, NHB}')
         ORDER  BY 1,2$$
    ) 
        AS
    t ( class int,                   -- needs a table alias!
        "HBW" float8,                -- resulting columns are double precision!
        "HBO" float8,
        "NHB" float8
        -- "value" double precision  -- column does not exist in result!
    );

A pivot table tends to aggregate some values given in an original table already, used to summarize large data flows. In PostgreSQL, it is a table returned with custom N value columns, which is the data type of the row summarized in our result.

Today we will be learning how to create a pivot table in PostgreSQL using a predefined table with existing values.

Use crosstab() to Create a Pivot Table in PostgreSQL

You can find crosstab() under the TABLE_FUNC heading in the PostgreSQL documentation. It is a function that returns a table with multiple rows.

Syntax:

crosstab ( sql text ) ? setof record

This query produces a pivot table containing row names plus N value columns, where N is determined by the row type specified in the calling query.

crosstabN ( sql text ) , setof table_crosstab_N

This query produces a pivot table containing row names plus N value columns. crosstab2, crosstab3, and crosstab4 are predefined.

Now, let’s see how we can use it. We will create a simple table called APARTMENT with columns; ID, UNIT, PRICE, AREA.

Example:

CREATE TABLE apartment (
   ID int PRIMARY KEY,
   UNIT int,
   PRICE int,
   AREA int
)

Now, let’s add a few values to our table:

INSERT INTO apartment VALUES (1, 20, 200, 10) , (2, 20, 200, 9), (3, 50, 190, 8);

So, if we look at our table now, it would be something as follows.

Output:

id    unit   price  area
1     20     200     10
2     20     200     9
3     50     190     8

And in our pivot table, this table would be represented as:

unit    ..180    190        200          210...
20         -      -      19/2 = 9.5       -
50         -      8         -             -

So here, we are taking the average of the AREA for each UNIT at their specific PRICES. We want to see the average AREA that we get on the PRICE mentioned.

So if you find the average AREA for a PRICE of 200 for a UNIT of 20, you’ll get the average as (10 + 9) / 2 = 8. In this way, you find the pivot table with the aggregates.

Now, you have understood the concept, so let us go ahead and implement it. We can write a query as follows:

Select UNIT, PRICE, avg(area)
from apartment
group by UNIT, PRICE

This query will implement the basic functionality of what we want. It uses the GROUP BY clause to take those columns in the table against which we wish to aggregate.

We wish to find the average of the AREA for a specific UNIT and PRICE; hence, we group using these two columns. A table is returned as follows.

Output:

unit   price        avg
50     190     8.0000000000000000
20     200     9.5000000000000000

Now, this table works properly as well, but let’s go ahead and use the crosstab(). You have to look at how it works and the constraints.

To use crosstab(), we have to ensure two crucial points:

1. crosstab() needs to have distinct values for each row.
2. crosstab() needs to have the same data types for each column.

So, make sure that no column has a different data type. Now, to pivot our results from this GROUP BY query, let’s go ahead and write something as follows:

select *
from crosstab
(
'Select UNIT::float, PRICE::float, avg(AREA)::float
from apartment
group by UNIT, PRICE') as ct(
    UNIT float,
    avge float
);

There are some critical points to consider here. We select the columns from the crosstab returned as the result table CT as defined by us with two columns: UNIT and avge.

After grouping, we get the columns from the APARTMENT and find the average. This query table is then pivoted for our final result.

Also, notice that we ensure to cast each column to the same data type. Of course, an average will either be a double-precision or float.

Hence, it is better to cast the int columns to float, even if unnecessary. If you run the query without casting, it will return an error as below.

Output:

ERROR:  return and sql tuple descriptions are incompatible
SQL state: 42601

Hence to avoid this, we make sure to cast to float or create a new table with values inserted into it with the type float. The difference in data types is what makes the tuple incompatible.

Output:

In other cases, there is no need to use crosstab(). Even using the GROUP BY is effective as it returns us a table with unique values for getting an average.

Without using the crosstab() function, we still would have gotten a result as follows:

unit   price   avg
50     190      8
20     200     9.5

You can notice in crosstab() that using a return list with three columns as we would want still returns us just values that contain the UNIT and average of the area. Why?

The SQL statement takes a set of categories and values, and in our case, the category becomes the UNIT, and the values tend to be the average column. Hence, only two columns are kept in our return table.

We can use it now that we know how crosstab() works for our needs. If we had UNITS with different PRICES and then took the AVG(), we would have an N number of columns, depending on our data.

Let’s change the values in our table to be as follows:

id    unit   price  area
1      20    200     10
2      20    170     9
3      50    190     8

Now, running the crosstab() query on this will return something as follows.

Output:

unit   avge   avge1
50      8    [NULL]
20     10      9

You can notice that changing the PRICE tends to spread out the AREA values into different averages. This happens because we also group our average by prices.

Same PRICE values will have an average of different values if present for all of them, but other PRICE values will only consist of averages of AREAS respective to each PRICE. Hence the table is sorted in this way.