Column "zone_id" does not exist

Question

Function in the PostgreSQL includes

When I am running through the query it is working fine like

Select * rpt.cst_data_integration_individuals(13, '2017-12-27T13:07:58Z', '2018-12-27T13:07:58Z', TRUE);

It is giving results.

But when I am trying through ADO.net, it is giving error like "zone_id" does not exists,

childrenQuery = "Select * from rpt.cst_data_integration_individuals(zone_id, period_start, period_end, is_child)";

Please someone help me.

@Andrew The value for zoneId is 13,an integer which it should take. — Ashish P., Feb 01 '19 at 06:15
I tried this approach https://stackoverflow.com/a/9481752/7276188 with Dapper but getting the same error. Is there something I am missing. I used for functions but. And that function I am trying to execute through query as childrenQuery — Ashish P., Feb 01 '19 at 06:21
`Select * from rpt.cst_data_integration_individuals(zone_id, period_start, period_end, is_child` You are missing `@` before `zone_id` and the other parameters. `(@zone_id, @period_start` This is a simple typo, thus I'd recommend people vote to close this question. — mjwills, Feb 01 '19 at 06:36
After adding the @ infront of that, I am getting error "No function matches the given name and argument types. You might need to add explicit type casts." — Ashish P., Feb 01 '19 at 06:47
That is a new question. Please write a new question for that. — mjwills, Feb 01 '19 at 07:16

score 1 · Answer 1 · answered Feb 02 '19 at 15:53

Your query:

Select * from rpt.cst_data_integration_individuals(zone_id, period_start, period_end, is_child)

Implies that zone_id, etc are fields available. If this were within a stored procedure and those were variables, it would likely work, but as it stands in your query it doesn't even make sense.

You need to invoke parameters in your actual childrenQuery with either the ':' or the '@' character.

Select * from rpt.cst_data_integration_individuals(:zone_id, :period_start, :period_end, :is_child)

Also, to help with debugging, declare your parameters explicitly with datatypes.

cmd.Parameters.Add(new NpgsqlParameter("zone_id", NpgsqlTypes.NpgsqlDbType.Integer));
cmd.Parameters.Add(new NpgsqlParameter("period_start", NpgsqlTypes.NpgsqlDbType.TimestampTz));
cmd.Parameters.Add(new NpgsqlParameter("period_end", NpgsqlTypes.NpgsqlDbType.TimestampTz));
cmd.Parameters.Add(new NpgsqlParameter("is_child", NpgsqlTypes.NpgsqlDbType.Boolean));

cmd.Parameters[0].Value = zoneId;
cmd.Parameters[1].Value = fromDate;
cmd.Parameters[2].Value = toDate;
cmd.Parameters[3].Value = true;

(There are golfed versions of the above, but I never bother -- I like the readabilty)

If you are using earlier version of Npgsql, they will probably be forgiving. If you are using recent version, they will give you a pretty clear error of where you went wrong. If zoneId is an integer (and true obviously matches a boolean datatype), then my guess would be there was a mismatch with how .NET sent the DateTime datatype -- maybe it assumed it was a timestamp without time zone.

Either way, the explicit parameter declarations will help, whereas AddWithValue is basically asking it to take its best guess.

Maybe for the time being avoid AddWithValue. I'm not going to lie, I use it from time to time, but you really shoud limit its use.

Column "zone_id" does not exist

1 Answers1